Different date periods with a Between Calendar Filter

[Alejandro]

Hello,

I need your help. I have been trying lots of things, but I could not manage to get what I want to:

I have this Between Calendar Filter in my report, but I want the widgets to behavior a bit different:

 

• The "Card" ones are working fine, just want to calculate the measure between the filter dates.
• At the Bar chart I want to display the period FROM: Last selected date -13 months TO: Last Selected date. (Keeping the monthly granularity). That is the point that I am not achieving.

Could you help me?

Thanks a lot

[v-yuezhe-msft · Accepted solution]

@Alejandro,

Please create a date table using DAX below. Ensure that there is no relationship between the date table and your original table.

date = CALENDARAUTO()

Create the following measure in your original table, and then use the date field of date table to create a slicer.

Last 13 month = var Ldate=MAX('date'[Date])
var Fdate=date(year(edate(Ldate,-13)),month(edate(Ldate,-13)),1)
return  IF(MIN('Yourtable'[DateKey])<Fdate,BLANK(),IF(MIN('Yourtable'[DateKey])>Ldate,BLANK(),yourcalculation))

There is a similar thread for your reference.
https://community.powerbi.com/t5/Desktop/Show-last-13-months-based-on-user-single-slicer-selection/t...

Regards,
Lydia

[Greg_Deckler]

I am thinking that you are going to have to disconnect your date slicer from that visual. Then, you could theoretically adjust your measure to do something like:

Measure = 
VAR __maxDate = MAX('Table'[Date]) //get the latest date in your slicer
VAR __minDate = DATE(YEAR(__maxDate)-1,MONTH(__maxDate)-1,DAY(__maxDate)) //approximately 13 months, probably a better way to do this but this is for an example.
VAR __table = FILTER('Table1','Table1[Date]>=__minDate && 'Table1[Date]<=__maxDate //create a filtered temp table
RETURN
//Do measure calculation stuff here based on your temp table.

OK, I don't have your measure formula so can't be more specific. But, in theory, this should only return a value for the measure for your desired date range.