Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Difference between 2 measures in matrix table

Hi All - I am beginner and started using PBI very recently. I am trying show 2 tables with weighted average measure and later trying to show the difference between 2 tables. The difference is not wor...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Please find the attached solution. The only diff is CDR Month 2.  To me, it seems like either  I forget to copy a line, or might be missing in the post.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Please share the formula and some sample data and sample output.

With a date table and Datesytd or totalytd, YTD (or this year last year can be found like given below) and then take diff of two measures.

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/