Virtual budget

[tanat_inc]

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.

QoQ MTD Revenue = ([MTD Revenue] / MTD LQ Revenue) -1

VAR today_day = DAY(LASTDATE(d_date_table[Date]))

VAR today_month = MONTH(LASTDATE(d_date_table[Date]))

VAR today_year = YEAR(LASTDATE(d_date_table[Date]))

VAR lq_month = IF(today_month-3 <= 0, today_month+9, today_month-3)

VAR lq_year = IF(today_month-3 <= 0, today_year-1, today_year)

VAR MTD LQ Revenue = CALCULATE(

'00_m_revenue'[Total Revenue],

DATEADD(d_date_table[Date],-3,MONTH),

d_date_table[day] <= today_day,

d_date_table[month_no] = lq_month,

d_date_table[year] = lq_year

)

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

[amitchandak · Accepted solution]

@tanat_inc , 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)))
)

[tanat_inc]

Thanks for solution, it's work.