Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would like to calculate a monthly value and its accumulation from a defined start-date on and display it over time.
My sample set is very simple but I am just not able to figure it out how to calculate it.
I linked the dataset to a calendar-table already and tried various ways to get the month since implementation in a dynamic way.
Another approach would be to have a measure to see "if calendar-date > implementation date, then savingsvalue, else 0".
Below is the DataSet and the 2 desired Measures i would like to create:
Solved! Go to Solution.
Hi @nexaframe
I delete the relationship between two tables, then you can refer to the three measures
ValuebyTime =
var _startdate=LOOKUPVALUE('DataSet'[startdate],[ID],MAX('DataSet'[ID]))
var _value=LOOKUPVALUE('DataSet'[value],'DataSet'[ID],MAX([ID]))
return IF(MAX('Calendar'[YYYY-MM])>FORMAT(_startdate,"YYYY-MM"),_value,0)
Sum_valuetime = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('DataSet'[ID]), ALLSELECTED('Calendar'[YYYY-MM])) , "v",[ValuebyTime])
var _cur_id = VALUES('DataSet'[ID])
var _cur_date = VALUES('Calendar'[YYYY-MM])
return SUMX(FILTER(_t,[ID] in _cur_id&&[YYYY-MM]<=MAX('Calendar'[YYYY-MM])) , [v])
Actual_sum_output = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('DataSet'[ID]), ALLSELECTED('Calendar'[YYYY-MM])) , "sums",[Sum_valuetime])
var _cur_id = VALUES('DataSet'[ID])
var _cur_date = VALUES('Calendar'[YYYY-MM])
return SUMX(FILTER(_t,[ID] in _cur_id&&[YYYY-MM] in _cur_date), [sums])
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nexaframe
You can refer to the following measure.
Cummulative value=SUMX(FILTER(ALL('Calendar'),[Date]<=MAX('Calendar'[Date])),[ValuebyTime])
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Yolo,
Thanks for your reply. I found the a similar measure also, but as it is showing in your screenshot, the totals are not correct.
I need the column and row totals to be displayed correct, so I can report the process over time...
I calculated the value like this:
Cumm_ValuebyTime =
CALCULATE(
[ValuebyTime],
FILTER(
ALLSELECTED('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
)
)
I think that I need to create some sort of table that would show the summarized data in a expanded way, so i can see the running total over all IDs?
I hope you could further support on that issue, as all my research and trials were dead ends...
Thank you
Hi @nexaframe
You can try the following measure
Measure 3 = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('DataSet'[ID]), ALLSELECTED('Calendar'[Date])) , "v",CALCULATE(SUMX(FILTER(ALLSELECTED('Calendar'),[Date]<=MAX('Calendar'[Date])),[ValuebyTime])))
var _cur_id = VALUES('DataSet'[ID])
var _cur_date = VALUES('Calendar'[Date])
return
SUMX(FILTER(_t,[ID] in _cur_id && [Date] in _cur_date) , [v])
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello,
i really tried now to figure my the issues out - but I can't manage to come close to the result that i would like to see in your example.
I uploaded the small pbix file to my google drive... hope you could help me out once more.
cummulativeCalculationbyDate.pbix
Hi @nexaframe
I delete the relationship between two tables, then you can refer to the three measures
ValuebyTime =
var _startdate=LOOKUPVALUE('DataSet'[startdate],[ID],MAX('DataSet'[ID]))
var _value=LOOKUPVALUE('DataSet'[value],'DataSet'[ID],MAX([ID]))
return IF(MAX('Calendar'[YYYY-MM])>FORMAT(_startdate,"YYYY-MM"),_value,0)
Sum_valuetime = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('DataSet'[ID]), ALLSELECTED('Calendar'[YYYY-MM])) , "v",[ValuebyTime])
var _cur_id = VALUES('DataSet'[ID])
var _cur_date = VALUES('Calendar'[YYYY-MM])
return SUMX(FILTER(_t,[ID] in _cur_id&&[YYYY-MM]<=MAX('Calendar'[YYYY-MM])) , [v])
Actual_sum_output = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('DataSet'[ID]), ALLSELECTED('Calendar'[YYYY-MM])) , "sums",[Sum_valuetime])
var _cur_id = VALUES('DataSet'[ID])
var _cur_date = VALUES('Calendar'[YYYY-MM])
return SUMX(FILTER(_t,[ID] in _cur_id&&[YYYY-MM] in _cur_date), [sums])
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xinruzhu-msft ,
I am trying to create similar kind of cummulative total with respect to today's date however i couple of scenario :
1) need the cummulative total of Qunatities if delivery date <= Today .
2) if Delivery date > today then it gove the qunatoty which is available in the column.
I tried the below measre but it's not taking Date column however when i tried to create a calculated column then it's working but i am getting sum of whole column(Quantity). Request you to please help is there any other way to achieve this .
Please see below futher:
Request you to please suggest .
Thanks,
Ashish
I figured out the MonthlySavingsSinceImplemenationDate:
ValuebyTime=
CALCULATE(
SUM(DataSet[value]),
USERELATIONSHIP('Calendar'[Date],DataSet[startdate])
MonthlySavingSinceImplementation=
CALCULATE(
[ValuebyTime],
FILTER(
ALLSELECTED('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |