Cumulative DAX to compare two financial years

[JamesGordon]

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

[amitchandak]

@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.