Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Static Baseline

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. 

 

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.

 

How can I keep my baseline number Static so i can use it to find a percent of baseline?

  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , you have to use all or allselected to make sure you get a value when month is in context

     

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

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , you have to use all or allselected to make sure you get a value when month is in context

     

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