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
Users online (3,485)