Dax Help for counting blank cells for average sales

[RRaj_293]

Hi All,

I have an issue. I'm tryin gto find the average of sales Units which is equal to sum(sales units) / number of selected months. This average sales unit is used as denominator for another calucaltion which is months cover = sum(stock oh hand)/ avergae sales units.

Months cover = sum(stock on hand)/Average sales units

Average sales Units = sum(sales units)/number of selected months.

This number of months is a dynamic number as the filter for date could be 'Next 6 months'/'Next 3 months' / CY-Month year/Next 12 Months and so on. These are options available in the date table. here's the current dax which works as expected -

VAR totlsales =  CALCULATE(

    SUM ('Sales - Forecast'[Sales_Units]),

    LASTDATE(

       CALCULATETABLE(VALUES('Date Snapshot'[Date]),'Date Snapshot'[RowsInSalesFC] = TRUE)

     ) )

VAR selmnths =

CALCULATE(

    COUNTROWS('Date'),

     LASTDATE(

            CALCULATETABLE(

                VALUES('Date Snapshot'[Date]), 'Date Snapshot'[RowsInSalesFC] = TRUE )

         ),

         'Date'[Date] IN

         VALUES('Sales - Forecast'[Forecast_Month_Key])

            )

VAR avgSales =

   DIVIDE(totlsales,selmnths,1)

RETURN

DIVIDE('Stock On Hand - Forecast'[Stock On Hand Units (Forecast)],avgsales,0)

The Issue with the above calculation is it doesnt not consider the dates which are a blank for sales forecast units. SO with a date filter of next 6 months if a style colour has sales units as below - 

CURRENt RESULT -

In the first style (1.) this calculation of avergae sales does a 20/4 = 5 as the Oct and Nov months sales Fcst Units is blank.

EXPECTED RESULT -

20/6  = 3.33

Note - This condition is used as we have forecasts the at the 1st of every month . 

   'Date'[Date] IN

         VALUES('Sales - Forecast'[Forecast_Month_Key])

So Date column have all dates day/month/year vice and relative date filters like current month year (which is equated to 1/1/1990 as shown in screenshot above), Next 6 months/Next 3months . Fact table sales Forecast has a monthly snapshot of all forecast months. Here's an exaample - The current month snapshot will have 1st of the month and the today's snapshot. 

Style	Forecast Month	Snapshot date	sales FCst Units
ABC	1/05/2025	21/05/2025	0.01
ABC	1/06/2025	21/05/2025	0.02
ABC	1/07/2025	21/05/2025	0.03
ABC	1/05/2025	1/05/2025	0.05
ABC	1/06/2025	1/05/2025	0.06
ABC	1/07/2025	1/05/2025	0.07
ABC	1/05/2025	1/04/2025	0.17
ABC	1/06/2025	1/04/2025	0.11

Please advice how to include the dates irrespective of what values they hold mainly blanks and get the avergae right.

[RRaj_293]

Thank you @DataNinja777 .. Your solutions looks good and almost right except that 'Date'[Date] field when all selected just pull day vice all the dates meaning for next 6 months it brings around 180 dates , so in similar lines I used another field which is CY-month start date which is only the first of every month as the forecast data is only at the month level. 

Thank you for guiding in the right direction. Issue resolved

[DataNinja777 · Accepted solution]

Hi @RRaj_293 ,

The issue with your current DAX measure is that it counts only the months that have non-blank sales forecast values, which leads to an inflated average when some forecast months are blank. To fix this, you need to make sure the denominator counts all the selected months from your date table, regardless of whether sales exist for them or not. This can be done by using ALLSELECTED('Date') which respects slicer filters like "Next 6 Months" or "CY" but includes all selected months even if they have no sales data.

You can keep your totlsales variable as is, since it should only sum actual sales values:

VAR totlsales =
    CALCULATE(
        SUM('Sales - Forecast'[Sales_Units]),
        LASTDATE(
            CALCULATETABLE(
                VALUES('Date Snapshot'[Date]),
                'Date Snapshot'[RowsInSalesFC] = TRUE
            )
        )
    )

Then, redefine the selmnths variable so it counts all the visible dates from the slicer selection, including those with no sales:

VAR selmnths =
    CALCULATE(
        COUNTROWS(VALUES('Date'[Date])),
        ALLSELECTED('Date')
    )

Now calculate the average sales and return your final result:

VAR avgsales = DIVIDE(totlsales, selmnths, 1)
RETURN
DIVIDE('Stock On Hand - Forecast'[Stock On Hand Units (Forecast)], avgsales, 0)

This approach ensures that even if some forecast months have blank values, they are still included in the count, giving you a realistic average that reflects all selected months.

Best regards,