Building a visual based on month slicer

[Anonymous]

Hi, 

I am trying to build a visual such that the screen has a month slicer and year slicer and another slicer called timeframe which has values like R1, R3, R6 and R 12. I should be able to select a month and year from the slicers and the R value from the timeframe slicer. For example if I select Mar 2022 from month and year slicer and select R3 from the third slicer the visual should only show data from Jan 2022- mar 2022(included).

So far I have made the calendar table as follows : 

Calendar = ADDCOLUMNS(CALENDARAUTO(),

"Year", YEAR([Date]),

"Month", MONTH([Date])

)

I have made another table called Period as follows:

Period = 

VAR lastonemonth = ADDCOLUMNS(

CALCULATETABLE(

'Calendar'

, DATESINPERIOD('Calendar'[Date],(DATE(YEAR(TODAY()), MONTH(TODAY()),1)-1),-2, MONTH )

)

,"Timeframe"

,"R1"

)

VAR lastthreemonths = ADDCOLUMNS(

CALCULATETABLE(

'Calendar'

, DATESINPERIOD('Calendar'[Date],(DATE(YEAR(TODAY()), MONTH(TODAY()),1)-1),-4, MONTH )

)

,"Timeframe"

,"R3"

)

VAR lastsixmonths = ADDCOLUMNS(

CALCULATETABLE(

'Calendar'

,DATESINPERIOD('Calendar'[Date],(DATE(YEAR(TODAY()), MONTH(TODAY()),1)-1),-7, MONTH )

)

,"Timeframe"

,"R6"

)

VAR lasttwelvemonths = ADDCOLUMNS(

CALCULATETABLE(

'Calendar'

,DATESINPERIOD('Calendar'[Date],(DATE(YEAR(TODAY()), MONTH(TODAY()),1)-1),-12, MONTH )

)

,"Timeframe"

,"R12"

) 

RETURN

UNION(lastonemonth,lastthreemonths, lastsixmonths, lasttwelvemonths)
I am getting the following output for the above period table code:

 This output is correct but I want to be able to choose the end month and year.

And I also have two disconnect month and year table for the slicers as follows:

Months = {("JAN","1"), ("FEB", "2"), ("MAR", "3"), ("APR", "4"), ("MAY", "5"), ("JUN", "6"), ("JUL","7"), ("AUG","8"), ("SEP", "9"), ("OCT","10"), ("NOV", "11"), ("DEC", "12")}

Years = {"2020","2021","2022"}

The problem is that in the period table I am not able to use the selected values from the month and year slicer and it is only working with today(). 

I made some changes in period table as follows but it doesn't work:

Period = 

VAR _date= DATE(Years[SelectedYear], Months[SelectedMonth], 1)

VAR lastonemonth = ADDCOLUMNS(

CALCULATETABLE( 'Calendar'

,DATESINPERIOD('Calendar'[Date], _date, -1, MONTH )

)

,"Timeframe"

,"R1"

)

VAR lastthreemonths = ADDCOLUMNS(

CALCULATETABLE( 'Calendar'

,DATESINPERIOD('Calendar'[Date], _date, -3, MONTH )

)

,"Timeframe"

,"R3"

)

VAR lastsixmonths = ADDCOLUMNS(

CALCULATETABLE( 'Calendar'

,DATESINPERIOD('Calendar'[Date], _date, -6, MONTH )

)

,"Timeframe"

,"R6"

)

VAR lasttwelvemonths = ADDCOLUMNS(

CALCULATETABLE( 'Calendar'

,DATESINPERIOD('Calendar'[Date], _date, -12, MONTH )

)

,"Timeframe"

,"R12"

)

But this code is giving just 4 lines of output like this:

This is the table model:

Any help would be appreciated.

Thanks.

[amitchandak]

@Anonymous , refer if approach in my blog can help

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

[Anonymous]

Hi Amit,
Your blog post is really insightful, but it might not work for me, could you please look at my code and tell me why DATESINPERIOD is not returning the correct table when I use the values captured from the slicer but it works when I use the today () function.

Thanks.