topic Re: Comparison of Running Totals in Fiscal Years in DAX Commands and Tips

Comparison of Running Totals in Fiscal Years

Anonymous — Thu, 19 Mar 2020 18:36:53 GMT

Hello,

I'm comparing Cumulative Sales (GMV) across two fiscal years in the area chart visual.

My measure for the Cumulative Sales that works as expected showing Cumulative GMV up to today's date:

Cumulative GMV = VAR LastSalesDate = CALCULATE( MAX( 'FY19+FY20'[RecRev Time]), ALL('FY19+FY20')) RETURN IF(SELECTEDVALUE('Date table'[Date]) > LastSalesDate, BLANK(), CALCULATE([Total GMV], FILTER(ALLSELECTED('Date table'[Date]), 'Date table'[Date]<=MAX('Date table'[Date]))))

I'm comparing two Fiscal Years , FY19 (Oct 2018-Sep 2019) and FY20 (Oct 2019-Sep 2020) and I built my own Date Table.

My goal is to get both FYs onto x axis and show comparison. So far I've put the following:

The area chart works fine for two consecutive FYs, however, I'd like FY20 to show from the start up to today's date. I like this chart a lot because when you hover over the lines, it shows individual days.

Below, I have only months on axis sorted by fiscal month , but the numbers and chart are wrong

Re: Comparison of Running Totals in Fiscal Years

Greg_Deckler — Fri, 20 Mar 2020 00:00:35 GMT

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Comparison of Running Totals in Fiscal Years

Anonymous — Fri, 20 Mar 2020 13:00:08 GMT

Greg_Deckler Thanks for this. Wish it helped answering my question.

Re: Comparison of Running Totals in Fiscal Years

Greg_Deckler — Fri, 20 Mar 2020 13:28:38 GMT

Would be happy to help but I am not going to hand type in source data to try to emulate the issue here and get a solution. Sample source data as text please.

Re: Comparison of Running Totals in Fiscal Years

Anonymous — Wed, 25 Mar 2020 13:29:15 GMT

I did more of a workaround which turned out to be working just fine. Comparing months dynamically.