Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
This is my first message in the Power BI community.
I have imported a SharePoint list which contains energy consumption figures (like: gas, electricity, etc.) per site, per month, as per 2013 till now. Now I am looking for a calculation which calculates the difference between the figures of a 'baseline/reference' year/month, with figures of the same month of a later year, so for example:
I can create a year over year comparison, but I don’t know how to ‘fix’ (like the $-$ in Excel) the baseline year. I tried Dateadd, but the number of years will vary (2014 vs. 2013 = -1, but 2018 vs. 2013 = -5). Furthermore, I was also thinking about creating a separate table for the baseline year, but then I didn’t got the time intelligence to work.
I hope you can help me, thanks!
Regards,
Martijn
Solved! Go to Solution.
Hi Cherie,
Thanks for your reply! Unforunatly I couldn't get your two measures to work, but I found an other solution via the seperate 2013 dataset I created. Via 'Lookupvalue' I get the 2013 values in the original dataset per site and month. After that, it was easy to create the difference calculation.
So issue solved. Thanks for your help!
Hi @MartijnB
You may try to create the two measures like below.For example:
2013 = CALCULATE(SUM(Data[value]),FILTER(Data,YEAR(Data[date])=2013))
2018 = CALCULATE ( SUM ( Data[value] ), FILTER ( ALL ( Data ), YEAR ( Data[date] ) = YEAR ( MAX ( Data[date] ) ) + 5 && MONTH ( Data[date] ) = MONTH ( MAX ( Data[date] ) ) ) )
Regards,
Hi Cherie,
Thanks for your reply! Unforunatly I couldn't get your two measures to work, but I found an other solution via the seperate 2013 dataset I created. Via 'Lookupvalue' I get the 2013 values in the original dataset per site and month. After that, it was easy to create the difference calculation.
So issue solved. Thanks for your help!
Hi @MartijnB
Glad to hear you've solved it, please accept the reply as solution to close this thread.
Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |