cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
orlandopowerbi
Frequent Visitor

SHOWING n last weeks of data from the MAX date filtered

Hello everyone,

 

I'm trying to display the last 4 weeks of data. In my report i have 3 date slicers for year, month and date.

 

So what i would like is that whichever date filter is applied, my visual takes the maximum date filtered and display 4 weeks back of data from that maximum, ignoring whatever month filter is applied. To be more specific, if the user filters for March, i would like to display 4 weeks back, that includes February, instead of only March numbers.

 

So far I've tried this:

 

I created a measure which calculates the maximum date.

 

Max Date = CALCULATE(MAX(Data[Date]))

 

Then, i edited the interactions so mi visual is not affected by any date slicer and create a measure like these:

 

Calls = 
VAR MAXDATE = [Max Date]
VAR MINDATE = [Max Date]-28
RETURN CALCULATE(SUM(Data[Inbound]),FILTER(Dates,(Dates[Date]<=MAXDATE && Dates[Date]>=MINDATE)))

 

So in my head, the measure would capture the Maximum date filtered so my Calls measure can retrieve only the desired data.

 

But my visual is ingoring the filter condition and diplaying everything as unfiltered.

 

Thank you very much in advance for any help or ideas provided.

 

 

 

 

 

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@orlandopowerbi , Seem correct at first look.

 

Try if this one can help

 

Calls =
VAR MAXDATE = MAXX(allselected(Data), Data[Date])
VAR MINDATE = [Max Date]-28
RETURN CALCULATE(SUM(Data[Inbound]),FILTER(Dates,(Dates[Date]<=MAXDATE && Dates[Date]>=MINDATE)))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you @amitchandak , but the result seems to do the same. The visual is not reacting at all to the date slicers.

 

The weird thing is that that MAXX(allselected(Data), Data[Date]) does get the desired date but its doesnt seem to work inside the filter. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors