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
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.