topic Re: Static Baseline in DAX Commands and Tips

Static Baseline

Anonymous — Mon, 01 Mar 2021 04:31:36 GMT

I'm trying to use the average of the Jan 2020 and Feb 2020 Units Sold per work day as a baseline to compare to the COVID Months.

I have a reporting period month table and a sales table.

To create my baseline I did the following (Showing Jan but did same for Feb):

sum units Sold Jan 2020 = CALCULATE(sum('SALES'[Units]), 'Monthly_Reporting_Period'[Month_Year_Abbr]="Jan 2020")

REPEATED FOR FEB

Jan Work Days = CALCULATE(sum('Monthly_Reporting_Period'[Period_Workday_Cnt]), 'Monthly_Reporting_Period'[Month_Year_Abbr]="Jan 2020")

REPEATED FOR FEB

Jan calc = divide([sum units Jan 2020], [Jan Work Days])

Feb calc = divide([sum units Feb2020], [Feb Work Days])

Baseline Calc= (Jan Calc+Feb Calc)/2

My baseline number is perfect when I put it in a matrix with no Months Specified.

When i compare to the actual sales per day in each month of 2020 I get different numbers for each of Jan and Feb, and blank for the remaining months.

How can I keep my baseline number static so I can use to find a precent of baseline?

Re: Static Baseline

rfigtree — Mon, 01 Mar 2021 07:30:05 GMT

BaseLine = Var BaseLineUnits = CALCULATE(sum('SALES'[Units]), filter(all('Monthly_Reporting_Period'[Month_Year_Abbr]), 'Monthly_Reporting_Period'[Month_Year_Abbr]="Jan 2020" || 'Monthly_Reporting_Period'[Month_Year_Abbr]="Feb 2020" ) ) Var BaseLineWorkDays= CALCULATE(sum('Monthly_Reporting_Period'[Period_Workday_Cnt]), filter(all('Monthly_Reporting_Period'[Month_Year_Abbr]), 'Monthly_Reporting_Period'[Month_Year_Abbr]="Jan 2020" || 'Monthly_Reporting_Period'[Month_Year_Abbr]="Feb 2020" ) ) return divide(BaseLineUnits ,BaseLineWorkDays)

Re: Static Baseline

Anonymous — Mon, 01 Mar 2021 14:09:08 GMT

The same thing is occuring that occured with my measures. I need a static number, so for example.

Jan Sales 100 Units, 22 work days

Feb Sales 90 Units, 20 Work Days

Jan Sales per day= 100/22= 4.55 Units per day

Feb Sales per day= 90/20= 4.5 Units per day

4.55+4.5=9.05/2= 4.523 Units per day Baseline

I want the 4.523 to show as my baseline and use it as my baseline for % of baseline.