cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alejandro Occasional Visitor
Occasional Visitor

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.

View solution in original post

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.


I have book! Learn Power BI from Packt


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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 123 members 1,607 guests
Please welcome our newest community members: