Calculating a Moving Average for 3 months without blank values in DAX Power BI

Question

I have Client Services table with [Billable Minutes] value.

I need to create [Moving Average for 3 months] measure, where the calculation will only happen - if there are values in all 3 months in [Billable Minutes] exist ! (See below)

If [Billable Minutes] value is blank in at least one month in my Client Services table, then I want [Moving Avg] will show 0 or blank!

My goal:

        Client Services table:                              Result:

       Period     [Billable Minutes]                       Period     [Moving Avg]
       2018-11                   200                       2019-01             200  
       2018-12                   300
       2019-01                   100

To reach my goal, I am using the following DAX expression for [Moving Avg 3 months]:

     Moving Avg 3 month = 
      
      VAR PeriodToUse = DATESINPERIOD('Calendar FY'[Date], LASTDATE('Calendar FY'[Date]), -3, MONTH)
      VAR Result = CALCULATE(DIVIDE([Billable Minutes], COUNTROWS ('Calendar FY')), PeriodToUse) 
      VAR  ZeroValue=IF(Minx('Client Services',[Billable Minutes])=0,0,Result)                                
         
         Return Result

But, unfortunately, my end result is:

        Client Services table:                              Result:

       Period     [Billable Minutes]                       Period     [Moving Avg]
       2018-11                   200                       2018-11             67  
       2018-12                   300                       2018-12            167
       2019-01                   100                       2019-01            200

So, it takes an existing values in Client Services table and divides them by the number of periods. For example for 2018-11, 2018-10, 2018-09 - it takes (200+0+0)/3=66.6 (67 rounded) But I need the Moving Avg would be empty for 2018-11, because there are no values for 2018-10, 2018-09 in the Client Services table (same - for 2018-12, should not calculate)

Please, HELP!

Updated:

Here is the solution for this (thx to the answer below) In order to check if there zero in the selected period, extra measures should be created:

     Billable Minutes 
     Moving Avg Prev Month = Calculate('Client Services'[Billable Minutes], 
                                        PREVIOUSMONTH('Calendar FY'[Date]))
                       
     Billable Minutes 
    Moving Avg 2nd Prev Month = Calculate
                             ('Client Services'[Billable Minutes Prev Month], 
                                        PREVIOUSMONTH('Calendar FY'[Date]))

Then, when you check whether there are zero values - you need to check it - not just for [Billable Minutes], but for [Billable Minutes] within 3 months period = within [Billable Minutes]+[Billable Minutes Prev Month]+ [Billable Minutes 2nd Prev Month]

See the updated below (worked perfect):

enter 
     Billable Minutes 3 Months Avg = 

      VAR PeriodToUse = DATESINPERIOD('Calendar FY'[Date], 
                        LASTDATE('Calendar FY'[Date]), -3, MONTH)
      VAR Result = CALCULATE(DIVIDE([Billable Minutes], 
                        COUNTROWS ('Calendar FY')), PeriodToUse)                                                                                                                      
      VAR NMonthsPeriodBlank = 
         if([Billable Minutes] = BLANK(),0,1) + 
         if([Billable Minutes Prev Month] = BLANK(),0,1) + 
         if([Billable Minutes 2nd Prev Month] = BLANK(),0,1)                                       

          RETURN IF(NMonthsPeriodBlank < 3, BLANK(), Result)

mkRabbani mkRabbani · Accepted Answer · 2020-09-30T04:33:29

Follow these below steps-

Step-1: Convert your Period column to Date considering 1st date of each month as below-

Step-2: Get back to report by clicking Close & Apply and create this below 4 measures-

total = SUM('Client Services'[Billable Minutes])

total prev = CALCULATE([total],PREVIOUSMONTH('Client Services'[Period]))

total second prev = CALCULATE([total prev],PREVIOUSMONTH('Client Services'[Period]))

3 month avergae = 

VAR devide_by = if([total] = BLANK(),0,1) + if([total prev] = BLANK(),0,1) + if([total second prev] = BLANK(),0,1)
VAR total_amount = [total] + [total prev] + [total second prev]

RETURN IF(
    devide_by < 3,
    BLANK(),
    total_amount/devide_by
)

Here is the final output-

==================== Solution in single measure ====================

You can convert all measures into 1 measure as below-

3 month average new = 

VAR this_month = SUM('Client Services'[Billable Minutes])

VAR prev_month = 
CALCULATE(
    SUM('Client Services'[Billable Minutes]),
    PREVIOUSMONTH('Client Services'[Period])
)

VAR second_prev_month = 
CALCULATE(
    SUM('Client Services'[Billable Minutes]),
    PREVIOUSMONTH(
        DATEADD(
            'Client Services'[Period],
            -1,
            MONTH
        )
    )
)

VAR devide_by = 
if(this_month = BLANK(),0,1) + 
if(prev_month = BLANK(),0,1) + 
if(second_prev_month = BLANK(),0,1)

VAR total_amount = this_month + prev_month + second_prev_month

RETURN IF(
    devide_by < 3,
    BLANK(),
    total_amount/devide_by
)

Calculating a Moving Average for 3 months without blank values in DAX Power BI

1 Answers