Power BI show all previous months based on the month selected in a filter

user_5300021 0
Viewed 42

I have this data

declare @tbl table (Months varchar(10), MonthNo INT)
insert into @tbl values
('Jan',1),('Feb',2),('Mar',3),('Apr',4),('May',5),('Jun',6) 
select * from @tbl

I import the data into power BI and write the DAX below

Condiction = IF(CalMonth[MonthNo]=1, "Jan",
            IF(CalMonth[MonthNo] in {1,2},"Feb",
             IF(CalMonth[MonthNo] in {1,2,3},"Mar",
             IF(CalMonth[MonthNo] in {1,2,3,4},"Apr",
             IF(CalMonth[MonthNo] in {1,2,3,4,5},"May",
             IF(CalMonth[MonthNo] in {1,2,3,4,5,6},"June"))))))

using the Condiction column in a filter I want to be able to select for example if I select Feb I will see

Jan
Feb

if I select Apr I should see

Jan
Feb
Mar
APR

currently if i select any month it filtered to that month only which i do not want. I want ever month before and including the month I select

Any Idea guys

when I filter for Jun current Output enter image description here

when I filter for Jun Expected output enter image description here

dax powerbi
1 Answers

You can create a calculated table like this:

Calc_Table =
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( CalMonth[Months] ),
        "Condiction",
            SWITCH (
                CalMonth[Months],
                "Jan", "Jan",
                "Feb", "Jan, Feb",
                "Mar", "Jan,Feb,Mar",
                "Apr", "Jan,Feb,Mar,Apr",
                "May", "Jan,Feb,Mar,Apr,May",
                "Jun", "Jan,Feb,Mar,Apr,May,Jun"
            )
    ),
    CalMonth[Months] = SELECTEDVALUE ( CalMonth[Months] )
)

If we run the code, It returns:

Calc_Column

then you can put [months] in a slicer, and put [conduction] in a card visual etc...

OR

You can directly add a calculated column named [Condiction] to your Table:

Condiction =
SWITCH (
    SELECTEDVALUE ( CalMonth[Months] ),
    "Jan", "Jan",
    "Feb", "Jan, Feb",
    "Mar", "Jan,Feb,Mar",
    "Apr", "Jan,Feb,Mar,Apr",
    "May", "Jan,Feb,Mar,Apr,May",
    "Jun", "Jan,Feb,Mar,Apr,May,Jun"
)

Then in the same way, put [months] in a slicer, and put [conduction] in a card visual

user_194690880
Related