Percent difference in visuals

[MRN8840]

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?

[v-lili6-msft · Accepted 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

[amitchandak]

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/

[MRN8840]

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?

[amitchandak]

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

[Mariusz]

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

[MRN8840]

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 🙂