Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.