cancel
Showing results for
Did you mean:
New Member

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/2020 1/10/2020 1000 B 1/11/2020 1/20/2020 1000 C 1/21/2020 1/30/2020 1000

I need the outcome to be like this:

 Date Value Cumulative value 1/1/2020 100 100 1/2/2020 100 200 1/3/2020 100 300 1/4/2020 100 400 1/5/2020 100 500 1/6/2020 100 600 1/7/2020 100 700 etc.... 100.... 800...

So far, I created a date table and was able to use a measure to calulcate the value for each date but I am struggling with the cumulative part. Here's the measure I used for the values distribution over time:

Value = CALCULATE(
sum (Data[values]),
FILTER(
Data,
Data[StartDate]<=max( 'DateTable'[Date])
&& Data[EndDate] >= max ('DateTable'[Date])))

Any help with this will be greatly appreciated.
1 ACCEPTED SOLUTION
Community Support

Hi  @MAbdelRazik ,

Create a table as below:

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

Then create 2 measures as below :

``````_Value =
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
6 REPLIES 6
Community Support

Hi  @MAbdelRazik ,

Create a table as below:

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

Then create 2 measures as below :

``````_Value =
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
Super User

@MAbdelRazik , check I have done a split in new table here.

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

Post that you can run cumulative

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
New Member

@amitchandak I have reached a similar outcome to what you have there. I am looking for a next step to have cumulative values. So, in November, the values will be your Oct and Nov added together and so on.

Super User

@MAbdelRazik So did you create a table for breaking things out or a measure? If a table, create a calculated column. If a measure, do measure aggregation and add a column using ADDCOLUMNS then filter down to the current in context date. The column calculation goes something like:

``Column = SUMX(FILTER('Table',[Date]<=EARLIER([Date]),[Value])``

Did you use something like Open Tickets to break things out?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
New Member

@Greg_Deckler So, I had the one table with StartDate, EndDate and valuePerDay. I will call this Data_table.

I created a table that has dates and I will call this DateSTable. I created a measure using this code:

ValuePerDay = CALCULATE(
sum (Data_Table[valuePerDay]),
FILTER(
Data_Table,
Data_Table[StartDate]<=max( 'DateSTable'[Date])
&& Data_Table[EndDate] >= max ('DateSTable'[Date])))

Now I have a visual which has all the dates from the DateSTable and the measured values. I am trying now to get the cumulative values.

I am not sure where to add the column suggested in your method below. I also can't seem to refer to the measure in a calculated column. I am sure I am doing something wrong but I am not sure what it is.
Super User

@MAbdelRazik OK, if that is your measure, we will go the measure route. So:

``````Cumulative Measure =
VAR __Date = MAX('DateSTable'[Date])
VAR __Table = FILTER(ALL('DateSTable'[Date]),[Date]<=__Date)
VAR __Table1 =
__Table,
"PerDay",SUMX(FILTER(Data_Table,Date_Table[StartDate]<=[Date]&& Data_Table[EndDate] >=[Date])
)
VAR __Table2 =
__Table1,
"Cumulative",SUMX(FILTER(__Table1,[Date]<=EARLIER([Date])),[PerDay])
)
RETURN
MAXX(FILTER(__Table2,[Date]=__Date),[Cumulative)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors