EDNA Forum
Good Day
I hope this email find you all well
Per the attached sample customer and BU sales data. I’m need help with creating a Dax measure to show the sales $ variance by year (2019 and 2020) for each customer. Also what is the best to show a YTD and MTD view for this analysis?
Thank you in advance for your help
Regards,
Variance Calculation.pbix (29.3 KB)
Given the current data model, there is no way to show YTD or MTD because your data only contains yearly totals. You would have to have individual sales orders with corresponding dates or at least monthly totals in order to do that.
For the variance in yearly totals, something like this should work given the current data model:
Variance =
VAR Sales2020 = CALCULATE(SUM('EDNA Variance Revenue between two years'[Sale USD Random]), 'EDNA Variance Revenue between two years'[Year] = 2020)
VAR Sales2019 = CALCULATE(SUM('EDNA Variance Revenue between two years'[Sale USD Random]), 'EDNA Variance Revenue between two years'[Year] = 2019)
RETURN
Sales2020 - Sales2019@DaveC is spot on that the granularity of your data does not allow you to do this. However if you were to make some assumptions regarding daily sales, you could convert the annual granularity to an estimated daily granularity that would allow you to do this calculation.
If I were going to go that route, I would do it in Power Query. This exact issue was the focus of Problem of the Week #4. Here’s the thread for that problem, where Enterprise DNA Expert @haroonali1000 explains the problem and the solution.
You could also do it in DAX. Here’s a video from @sam.mckay that provides an excellent approach to accomplishing it that way.
I hope this is helpful.
– Brian
Dave C
Much appreciate your quick response per this matter. Will give your solution a try.
Thank you