Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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.
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.
@MAbdelRazik , check I have done a split in new table here.
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.
@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:
@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)
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |