Cumulative value of a constant value

[Anonymous]

Hi,

I am trying to create a Cumulative Sum of a constant value of a parameter table with no relation with my Fact Table, neither my Date Table, in order to calculate a cumulative value of that value for each week. It would be something like this:

Constant Value = 0,50

• Value for week 1 = 0,50
• Value for week 2 = 1,00
• Value for week 3 = 1,50
• …..

The object I am aiming is to represent this Objetive Cumulative Value  (Green dot line) vs my Current Cumulative Value (Solid orange line) of my Fact Table, looking something like this:

Any ideas how to figure out the metric to calculate the cumulative value for my constant?

Thanks a lot in advanced!

[v-diye-msft · Accepted solution]

Hi @Anonymous 

I created a sample as your requested, there’s date table relates to fact table1 ,and I add another new table2 managing the relationship 1:1 with fact table1.

Cumulative value1 = CALCULATE(SUMX(Table1,Table1[Value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table1[week]))

Cumulative value2 = CALCULATE(SUMX(Table2,Table2[value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table2[weeknum]))

(week and weeknum is calculated columns using Weeknum().)

Then add both 2 cumulative measures to the values pane, and format the second one.

Best regards,

Dina Ye

[v-diye-msft]

HI @Anonymous ,

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye

[Anonymous]

Hi @v-diye-msft 

Sorry not to answer before, I've been out for holidays 🙂

Finally, I approached this issue in a different way. It was an idea i get from @marcorusso in www.daxpattern.com :

You can take a look here

I will  also try your solution and let you know if it works.

Anyway, thanks a lot for your help.