Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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. 
 
How can I keep my baseline number static so I can use to find a precent of baseline?

2 Replies

  • rfigtree's avatar
    rfigtree
    Resolver III
    5 years ago
    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)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.