Percentage calculation with two date periods

[edlng]

Hi!

I'm newly using PowerBI and I've been struggling on obtaining some comparative and percentage values.

The data I have is an energy cost per month on three sites, for two energy types.

I created a slicer for my first period (P1) and a second for the period I want to compare it with (P2). The date periods are not limited to specific ones, they can be adjusted freely.

I would like to calculate the savings in € and % in P1 compared with P2.
The results I want to obtain are:

- total cost without savings

- total savings in €

- % savings from last year total cost

Do you know how I could do that?

Thank you so much!

[v-csrikanth · Accepted solution]

Hi @edlng 
As I have replicated your scenario with the assuming sample data and attached the pbix file below.

Assumed Period 1 (P1) and Period 2 (P2). I wanted to show the savings in € and %, depending on the date the user selects for each period.
Here's how I built it step-by-step without bookmarks, fully dynamic:

1. Created two separate slicers (Period1 and Period2) by making calculated tables from the main Date table:
   

   DAX

   CopyEdit

   Period1 = DISTINCT('DateTable'[Date])

   Period2 = DISTINCT('DateTable'[Date])

   These slicers allow users to select two different dates freely.

2. Captured the selected values from each slicer using measures:
   

   DAX

   CopyEdit

   P1 Selected = MAX('Period1'[Date])

   P2 Selected = MAX('Period2'[Date])

3. Created measures to calculate energy cost for each period:

   DAX

   CopyEdit

   P1 Cost (€) = CALCULATE(SUM('Data'[Cost (€)]), 'Data'[Date] = [P1 Selected])

   P2 Cost (€) = CALCULATE(SUM('Data'[Cost (€)]), 'Data'[Date] = [P2 Selected])

4. Calculated savings and % savings:

   DAX

   CopyEdit

   Savings (€) = [P1 Cost (€)] - [P2 Cost (€)]

   % Savings = DIVIDE([Savings (€)], [P2 Cost (€)], 0)

5. Added card visuals to show:

   Total cost in P2 (baseline)

   Total savings in €

   % savings from P2

   Added both slicers to the report so users can flexibly select any two periods to compare.

This setup gives a fully dynamic, user-driven way to compare any two dates without hardcoding periods or using bookmarks. It works great for trend analysis, energy savings, and cost comparisons. Hope this helps others trying to achieve something similar.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[edlng]

Hello @v-csrikanth, 
I have a problem with the results for the savings formula: Savings (€) = [P1 Cost (€)] - [P2 Cost (€)]
The total result is additioning the total cost for both periods, but it's not calculating the difference between the two years.
What I wish to have is the savings for P1, and P2 to be implicit. Like: "1 | Gas | 2024 | -4,254,125€"
Shouldn't there be a relationship between the two periods in the formula for it to happen?

[edlng]

Hi @v-csrikanth, thank you for your answer!
I got this error message when trying to write this measure "P1 Cost (€) = CALCULATE(SUM('Data'[Cost (€)]), 'Data'[Date] = [P1 Selected])". Do you know what went wrong?

Thank you!

[Vijay_Chethan]

you missed bracket for sum function

[edlng]

I copied and pasted it again but there's another error message:

[Vijay_Chethan]

try this:
P1 Cost (€) = CALCULATE(SUM('Data'[Cost (€)]), filter('Data',[Date] = [P1 Selected]))

[edlng]

It says that too few arguments were passed to the filter function, as the minimum argument count is 2

[edlng]

It worked, I had forgotten the coma. Thanks!

[Vijay_Chethan]

are you sure you used same dax measure as above, i would say copy and paste it if you have manually typed

[edlng]

Thank you very much! @Vijay_Chethan @v-csrikanth 

[Vijay_Chethan]

hello edlng,
might be really helpful if you could provide some test data to understand your usecase better

[edlng]

Hi @Vijay_Chethan 

Yes of course, here's a sample: 

Site Id	Year	Energy Id	Energy type	Cost
1	2020	1	Electricity	€          217.201,00
1	2020	2	Gas	€           42.222,00
2	2020	1	Electricity	€         424.204,00
2	2020	2	Gas	€           42.404,00
3	2020	1	Electricity	€          442.112,00
3	2020	2	Gas	€           32.442,00
1	2021	1	Electricity	€           144.011,00
1	2021	2	Gas	€            14.044,00
2	2021	1	Electricity	€           114.011,00
2	2021	2	Gas	€           24.227,00
3	2021	1	Electricity	€          145.712,00
3	2021	2	Gas	€             14.751,00