cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MAbdelRazik
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

TaskStartEndValue
A1/1/20201/10/20201000
B1/11/20201/20/20201000
C1/21/20201/30/2020

1000

 

I need the outcome to be like this:

DateValueCumulative value
1/1/2020100100
1/2/2020100200
1/3/2020100300
1/4/2020100400
1/5/2020100500
1/6/2020100600
1/7/2020100700
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
v-kelly-msft
Community Support
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 = 
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:

Screenshot 2020-09-15 155143.png

For the related .pbix file,pls see attached.

 

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

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
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 = 
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:

Screenshot 2020-09-15 155143.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
amitchandak
Super User
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

@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.

Greg_Deckler
Super User
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 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

@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)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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