Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to Solution.
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
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
You 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
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?
Hi @Mariusz!
Thanks for your reply!
All the slicers come from the same date-table, but the two on the top only interact with the three top visuals and the two on the bottom only interact with the three bottom visuals.
So yes, I show different data by switching interactions on/off 🙂
User | Count |
---|---|
83 | |
69 | |
68 | |
65 | |
54 |
User | Count |
---|---|
93 | |
91 | |
91 | |
76 | |
70 |