Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Week to Date Measure

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 evoluti...
Need Help
  • AlexChen's avatar
    AlexChen
    10 years ago

    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

AlexChen's avatar
AlexChen
Microsoft Employee
10 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

  • tyjames05's avatar
    tyjames05
    Helper I
    2 years ago

    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?