Forum Discussion
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:
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,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!- Greg_Deckler
Community Champion
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
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.- Greg_Deckler
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 = ADDCOLUMNS( __Table, "PerDay",SUMX(FILTER(Data_Table,Date_Table[StartDate]<=[Date]&& Data_Table[EndDate] >=[Date]) ) VAR __Table2 = ADDCOLUMNS( __Table1, "Cumulative",SUMX(FILTER(__Table1,[Date]<=EARLIER([Date])),[PerDay]) ) RETURN MAXX(FILTER(__Table2,[Date]=__Date),[Cumulative)
- amitchandak
Super User
@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.
