Week to Date Measure

[Anonymous]

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!

[AlexChen · Accepted 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

[tyjames05]

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?

[Anonymous]

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.

[Anonymous]

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!