Forum Discussion
Virtual budget
Hi community,
I have run a data set with sale and budget for a while and we usually benchmark with bueget and traditional YoY compare.
However, COVID-19 make last-year data an exception and can't be used to compare. Also it's COVID-19 again that make my company sale significantly below proposed budget.
We come to agreement that we will use everage of 2021-Jan as base to calculate virtual budget. We will use [2021Q1 sale + 10% growth] as virtual budget for every month of Q2 and use [2021 Q1 sale + 30% growth] as virtual budget of Q3 and Q4.
Question is : How to create measure to use average of 2021 as budget.
- Company doesnot agreed to always compare MTD with 2021-Jan. They want flat target line, not as distribution as Jan.
- Work around in my mind is to create calculated column, but that target won't be able to drill down as calculated measure target.
Below is the code I use to compare MTD QoQ.
The code look messy because if I use only CALCULATE(xxx, DATEADD(d_date_table[Date],-3,MONTH) --> it will compare this month MTD with last-3-month full month data. Would be appreciate if you can make it more effective.
Thanks
Anonymous , based on what I got, Try a meaure like
Switch(true(),
max(Date[year Qtr]) = "2021Q1" ,CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(eomonth(dateadd('Date'[Date],-1*(month(today)-1),month),0)))
max(Date[year Qtr]) = "2021Q2" ,CALCULATE(Averagex(values('Date'[Month year]),Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
)
2 Replies
- amitchandak
Super User
Anonymous , based on what I got, Try a meaure like
Switch(true(),
max(Date[year Qtr]) = "2021Q1" ,CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(eomonth(dateadd('Date'[Date],-1*(month(today)-1),month),0)))
max(Date[year Qtr]) = "2021Q2" ,CALCULATE(Averagex(values('Date'[Month year]),Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
)