Forum Discussion

MAbdelRazik's avatar
MAbdelRazik
New Member
6 years ago
Solved

Distributing values between start and end dates and get the cumulative

I am trying to distribute values between dates. I have a start and finish date and a certain value that I want distributed between these 2 dates. For example Task Start End Value A 1/1/202...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  MAbdelRazik ,

     

    Create a table as below:

    Table 2 = CALENDAR(MIN('Table'[Start]),MAX('Table'[End]))

    Then create 2 measures as below :

    _Value = 
var _table=ADDCOLUMNS('Table 2',"value",CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),'Table'[Start]<='Table 2'[Date]&&'Table'[End]>='Table 2'[Date])))
Return
MAXX(_table,[value])
    _Cumulative value = SUMX(FILTER(ALL('Table 2'),'Table 2'[Date]<=MAX('Table 2'[Date])),'Table 2'[_Value])

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi  MAbdelRazik ,

 

Create a table as below:

Table 2 = CALENDAR(MIN('Table'[Start]),MAX('Table'[End]))

Then create 2 measures as below :

_Value = 
var _table=ADDCOLUMNS('Table 2',"value",CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),'Table'[Start]<='Table 2'[Date]&&'Table'[End]>='Table 2'[Date])))
Return
MAXX(_table,[value])
_Cumulative value = SUMX(FILTER(ALL('Table 2'),'Table 2'[Date]<=MAX('Table 2'[Date])),'Table 2'[_Value])

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!