cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alejandro New Member
New Member

Different date periods with a Between Calendar Filter

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:

 

PowerBI Issue.png 

 

 

  • 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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Different date periods with a Between Calendar Filter

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User
Super User

Re: Different date periods with a Between Calendar Filter

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Moderator v-yuezhe-msft
Moderator

Re: Different date periods with a Between Calendar Filter

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.