calculate running total for YOY and YOY at a specific point in time

[dw700d]

I am creating a budget vs actuals dashboard  and I am focused on head count and expense. My data comes from 5 seperate tables including a date table. I would like help with creating four different measures. Thanks in advance for your assistance.

Measure 1 – I need to calculate  the variance between a running total of actual expenses vs a running total of budgeted expenses for the most recent year in my data set. Currently the most recent year in my data set is 2022.( In a few weeks however, I will have 2023 data and next year 2024 etc etc.). This calculation should be based on the most recent month of actuals. For example although my data set below has budgeted expenses until  12/1/2022 the measure would only include a running total  until 4/1/2022 because I only have 4 months of actuals in 2022. Based on the data below the variance would be 12,000 (70,000-58,000).

Measure 2 - – I need to calculate  the variance between a running total of the most recent years actual expenses vs a running total of the prior years actual  expense. Currently the most recent year in my data set is 2022.( In a few weeks however I will have 2023 data and next year 2024 etc etc.). This calculation should be based on the most recent month of actuals. the measure would only include a running total  until 4/1/2022 because I only have 4 months of actuals. Based on the data below the variance would be 14000 (44,000-58,000)

Budget
Date	budgeted expense
1/1/2021	10000
2/1/2021	11000
3/1/2021	12000
4/1/2021	12000
5/1/2021	13000
6/1/2021	14000
7/1/2021	12500
8/1/2021	20000
9/1/2021	21000
10/1/2021	10000
11/1/2021	11000
12/1/2021	12000
1/1/2022	13000
2/1/2022	18000
3/1/2022	19000
4/1/2022	20000
5/1/2022	10000
6/1/2022	11000
7/1/2022	12000
8/1/2022	10000
9/1/2022	10000
10/1/2022	10000
11/1/2022	10000
12/1/2022	11000

Actuals
Date	Actual expense
1/1/2021	9000
2/1/2021	10000
3/1/2021	12000
4/1/2021	13000
5/1/2021	15000
6/1/2021	11000
7/1/2021	10000
8/1/2021	20000
9/1/2021	21000
10/1/2021	19000
11/1/2021	12000
12/1/2021	11000
1/1/2022	10000
2/1/2022	15000
3/1/2022	15000
4/1/2022	18000
5/1/2022
6/1/2022
7/1/2022
8/1/2022
9/1/2022
10/1/2022
11/1/2022
12/1/2022

Measure 3 – I need to calculate  the variance between the most recent month's headcount vs the most recent month's budgeted headcount for the most recent year in my data set. Currently the most recent year in my data set is 2022.( In a few weeks however I will have 2023 data and next year 2024 etc etc.). This calculation should be based on the most recent month's headcount. For example, although my data set below has budgeted headcount until  12/1/2022 the measure would only calculate headcount on  4/1/2022 because I only have 4 months of actuals. Based on the data below the variance would be 22 (425-403) (***This calculation does not use running total)

Measure 4 - – I need to calculate  the variance between the most recent year head count vs the prior year’s headcount . Currently the most recent year in my data set is 2022.( In a few weeks however I will have 2023 data and next year 2024 etc etc.). This calculation should be based on the most recent month’s headcount. the measure would only include  the month of 4/1/2022 because I am in the 4th months of actuals. Based on the data below the variance would be 33 (403-370) (***This calculation does not use running total)

Head count          Budget
date	Head count
1/1/2022	100
2/1/2022	105
3/1/2022	110
4/1/2022	110
5/1/2022	100
6/1/2022	129
7/1/2022	100
8/1/2022	130
9/1/2022	100
10/1/2022	100
11/1/2022	120
12/1/2022	110

Head count Actuals
date	Head count
1/1/2021	95
2/1/2021	98
3/1/2021	100
4/1/2021	110
5/1/2021	100
6/1/2021	200
7/1/2021	100
8/1/2021	120
9/1/2021	130
10/1/2021	150
11/1/2021	110
12/1/2021	110
1/1/2022	100
2/1/2022	100
3/1/2022	90
4/1/2022	80
5/1/2022	75
6/1/2022	40
7/1/2022	100
8/1/2022	120
9/1/2022	110
10/1/2022	190
11/1/2022	180
12/1/2022	110

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  I have solved the first 2 questions.  Apply the same principle to solve the other 2.

Hope this helps.

[amitchandak]

@dw700d , Create measure like with help from date Table

Cumm Sales =

Var _max = maxx(allselected(Sales), Sales[sales Date])

return

CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date]) && Date[Date] <= _max) )

or

Cumm Sales =

Var _max = maxx(allselected(Sales), Sales[sales Date])

return

if(max(Date[Date]) <=_max ,

CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])) ) , Blank())

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc