topic Cumulative DAX to compare two financial years in DAX Commands and Tips

Cumulative DAX to compare two financial years

JamesGordon — Wed, 14 Oct 2020 16:35:27 GMT

Hi all,

I am trying to plot two lines on a “Line and clustered column chart”. I would like one line to display the cumulative margin % of last financial year (1/10/2019 – 30/09/2020). And the second line to display this Financial year (1/10/2020 – today).

I have tried the following DAX thinking it might automatically split into two line (which is does if I choose a “Line chart”

Cumulative Margin % YOY = CALCULATE(SUM(Sheet[Margin (filtered)])/sum(Sheet[Total Sell]),FILTER(ALLSELECTED(Dates[Date]),Dates[Date] <= max (Dates[Date])),VALUES(Dates[Fiscal Year]))

However using this DAX in the a “Line and clustered column chart” only creates one line with the wrong values.

So I think I need to create two separate measures – one for FY19 and one for this FY – my issue is I’m struggling to create a cumulative DAX that will only cover the period (1/10/2019 – 30/09/2020). This is the DAX I am using currently

Cumulative Margin % = CALCULATE(SUM(Sheet[Margin (filtered)])/sum(Sheet[Total Sell]),

FILTER(ALLSELECTED(Dates[Date]),Dates[Date] <= max (Dates[Date])))

Any help would be appreciated

Re: Cumulative DAX to compare two financial years

amitchandak — Thu, 15 Oct 2020 01:48:54 GMT

JamesGordon , if you want cumulative on Year on Year, we can use datesytd like

Margin % = divide(SUM(Sheet[Margin (filtered)]),sum(Sheet[Total Sell])) YTD Sales = CALCULATE([Margin %],DATESYTD('Date'[Date],"12/31")) Last YTD Sales = CALCULATE([Margin %],DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) This year Sales = CALCULATE([Margin %],DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) Last year Sales = CALCULATE([Margin %],DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE([Margin %],DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE([Margin %],dateadd('Date'[Date],-1,Year)) //Only year vs Year, not a level below This Year = CALCULATE([Margin %],filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE([Margin %],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1)) diff = [This Year]-[Last Year ]

Only Cumm

Cumm Sales = CALCULATE([Margin %],filter(allselected(date),date[date] <=max(date[date])))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.