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

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?