Rollover calculations in PowerBI

0
votes

I am very new to PowerBI and exploring it. I came across a sample data which has a start date and end date, Group Type and a Value. Basically its something like, an exam group has a start date and end date with a score.

I want to do a rollover calculation like when I view the dashboard it needs to provide me value for this month and for future months. The values needs to be added based on the start date and end date. For example, if the start date is 01-01-2020 and end date is 12-07-2020 and the score is 20 for one record and the start date is 02-03-2020 and end date is 31-05-2020 and the score is 09 for another record, the table needs to show something like for May 29, June, July - 20.

For current months the score must be added cumulatively and for the future months it has to show the cumulative score from that month to the remaining months excluding the current month.

Below is my expected output

Expected output

Source Data is

Source Data

It would be helpful for me if anyone can guide me what logic I need to use for this?

Source File

1
powerbi

1 Answers

0
votes

You can do this by adding an extra column where you sum all totals from earlier end dates:

rollover = 
var lDate = Sheet1[End Date]
var place = Sheet1[Placement]
var total = CALCULATE(sum(Sheet1[Total]),filter(Sheet1, place = Sheet1[Placement] && lDate >= Sheet1[End Date]))
return total

result:

enter image description here This you can bring into your visual..