Forum Discussion
Percent difference in visuals
Hi!
I have a page looking like this:
In the top and bottom section I can choose a week ('Uge i år') in a year ('År') and compare the consumption of electricity, water and heat.
How do a create a visual (card) which shows the difference in % between the top and bottom views?
In the example above I have chosen week 12 last year and this year. As you can see the difference is pretty big (thanks Corona... 😞 ).
-43,604 KWh (electricity)
-354 M3 (water)
-49 MWh (heat)
I work in the Facility Management at Aarhus University (Denmark), and I want to show how much we have 'powered down' our building due to corona.
Any hints?
hi MRN8840
For your case, you'd better use this logic to create a last year measure to get it:
Last year = CALCULATE(SUM('Table'[electricity]),FILTER(ALL('Date'[Year]),'Date'[Year]=MAX('Table'[Year])-1))Percent L/C = DIVIDE([Last year],CALCULATE(SUM('Table'[electricity])),0)Result:
here is sample pbix file, please try it.
By the way, for the next year measure, you could try this logic:
Next year = CALCULATE(SUM('Table'[electricity]),FILTER(ALL('Date'[Year]),'Date'[Year]=MAX('Table'[Year])+1))Regards,
Lin
6 Replies
Hi MRN8840
Are the top and bottom slicers independent from each other or are these coming from the same table and you show different data by switching the interactions between visuals?
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedInYou can use this year vs last year measure and find diff
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))use date calendar
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/Hi amitchandak!
I already have a date calender and it has been marked so in the model view. I have also joined it with the dataflow:
I have thought of using YTD, but will that change when I change the slection on the slicers?
Yes, In fact you keep one slicer and make both of the Work on this year last year
. If you want to use two. Use the 2020 slicer with change %. hopefully this is done using interactions
hi MRN8840
For your case, you'd better use this logic to create a last year measure to get it:
Last year = CALCULATE(SUM('Table'[electricity]),FILTER(ALL('Date'[Year]),'Date'[Year]=MAX('Table'[Year])-1))Percent L/C = DIVIDE([Last year],CALCULATE(SUM('Table'[electricity])),0)Result:
here is sample pbix file, please try it.
By the way, for the next year measure, you could try this logic:
Next year = CALCULATE(SUM('Table'[electricity]),FILTER(ALL('Date'[Year]),'Date'[Year]=MAX('Table'[Year])+1))Regards,
Lin
