Running Total for Last Quarter by fiscal week

[Anonymous]

Hi, 

I am calculating the running total based on fiscal calendar, calculated by fiscal week. Can someone help explain why my DAX is not returning the cumulative numbers, instead its showing the billings for last quarter?

My DAX formula is as below:

Total Billings LQ running total = 
CALCULATE(
	[Billings LQ],
	FILTER(ALLSELECTED(Dates[FW in Year]),
    Dates[FW in Year]>= MAX(Dates[FW in Year])
))

Quick screenshot for the sample pbix:

Sample pbix file also attached: Running Total. Appreciate all the help! 

Thanks,
Dennis

[mahoneypat]

Your Filter( ) expression is not removing all the filters put in place by your table visual.  For example is you only had the FW in Year column and your measure, you should see your expected behavior.  Consider using ALLSELECTED(YourTable) in your Calculate too, but you may need more targeted filter removal if that doesn't work.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat, 

Can you please show me your solution in the pbix file that I attached in the original post? I pulled in FW in Year column in the table and still did not work. Change the calculation to ALLSELECTED(Date Table) did not work either and it returned null values for all weeks. 

Thanks,
Dennis 

[amitchandak]

@Anonymous , please find the attached file after the signature. I have done a few changes to the calendar to create Rank, Qtr start Date etc. And added new columns to your visual

Let me know if this solves your purpose.

Inline with my videos

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
https://www.youtube.com/watch?v=7Jc3D4iaTqs

[Anonymous]

@amitchandak your solution works perfectly! Appreciate your help!