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.
Hello.
I am trying to get a WTD measure on Power BI. I have a separate Calendar table and I have been trying some formulas, with no success so far.
What I am pursuing is basically the evolution on the selected week comparing to the previous one. On the Report I have a filter where you select just one week. The Calendar table has a Column with the week number (Calendar[Week]).
The last I tried, among others, was this one:
Weekly Evolution = ( sum(NG_Consumptions[Consumption]) - CALCULATE(sum(NG_Consumptions[Consumption]);('Calendar'[Week])-1)) / CALCULATE(sum(NG_Consumptions[Consumption]);('Calendar'[Week])-1)
This doesn't work. Could you please shed some light on this matter? Thank you in advance!
Solved! Go to Solution.
Hi,
I assume your “NG_Consumptions” table looks like below.
1. You need to create a measure to calculate sum consumption of current week.
sumConsumption = CALCULATE(SUM(NG_Consumptions[consumption]), ALLEXCEPT(NG_Consumptions, NG_Consumptions[week]))
2. create a measure to calculate sum consumption of last week.
previousWeekSumAmount = CALCULATE(SUM(NG_Consumptions[consumption]), FILTER(ALL(NG_Consumptions), SUMX(Filter(NG_Consumptions, NG_Consumptions[week] = EARLIER(NG_Consumptions[week]) + 1), NG_Consumptions[consumption])))
3. create a measure to calculate your Weekly Evolution
Weekly Evolution = if([previousWeekSumAmount] = 0, BLANK(), ([sumConsumption] - [previousWeekSumAmount])/[previousWeekSumAmount])
This is the result:
Best Regards
Alex
Hi,
I assume your “NG_Consumptions” table looks like below.
1. You need to create a measure to calculate sum consumption of current week.
sumConsumption = CALCULATE(SUM(NG_Consumptions[consumption]), ALLEXCEPT(NG_Consumptions, NG_Consumptions[week]))
2. create a measure to calculate sum consumption of last week.
previousWeekSumAmount = CALCULATE(SUM(NG_Consumptions[consumption]), FILTER(ALL(NG_Consumptions), SUMX(Filter(NG_Consumptions, NG_Consumptions[week] = EARLIER(NG_Consumptions[week]) + 1), NG_Consumptions[consumption])))
3. create a measure to calculate your Weekly Evolution
Weekly Evolution = if([previousWeekSumAmount] = 0, BLANK(), ([sumConsumption] - [previousWeekSumAmount])/[previousWeekSumAmount])
This is the result:
Best Regards
Alex
Hello all, I came across this thread and decided to try it out. I get the following error when I try to use this measure with the guage visual.
Any ideas?
Hi Alex,
can you help me?, i used your solution and it's very effective, really appreciate it, but i have troubles when one week is missing, for example having week 44 and next is 46, when this happens the measure "previousWeekSumAmount" value is 0 for the week 46, and the measures fails, i have tried adding some conditionals but haven't success.
Alex,
Thanks so much for your response.
In the mean time, I tried some formulas and one that works is this one:
Weekly Evolution = (SUM(NG_Consumptions[Consumption])-CALCULATE(sum(NG_Consumptions[Consumption]),DATEADD('Calendar'[Date],-7,DAY)))/CALCULATE(sum(NG_Consumptions[Consumption]),DATEADD('Calendar'[Date],-7,DAY))
And it gives the value of the incomplete previous week, which is good, it compares similar periods.
But when I do that for the month, with the formula below, it compares the whole previous month with the current month, different periods.
Monthly Evolution = (TOTALMTD(sum(NG_Consumptions[Consumption]),'Calendar'[Date])-TOTALMTD(sum(NG_Consumptions[Consumption]),DATEADD('Calendar'[Date],-1,MONTH)))/TOTALMTD(sum(NG_Consumptions[Consumption]),DATEADD('Calendar'[Date],-1,MONTH))
Any idea why this happens?
Thanks!
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |