DAX Count Months in Quarter for Average

[don_writer]

Hi all,

I am missing something fundamental here. 
I have a table called srcActive with 100 rows. The columns are srcActive[data_as_of], srcActive[EmployeeID], srcActive[headcount].

I have a Date Dimension table named refDate. Lets say those columns are refDate[Date], refDate[MonthInQtr]

These tables has a releationship between srcActive[data_as_of] and refDate[Date]. Slicer all work wonderfully.

HeadcountQtr = 
DIVIDE(
    CALCULATE(
        SUM(srcActive[headcount]),
        DATESQTD(srcActive[data_as_of]))
            ,
        3        
        )

Works like a charm except for one thing. If the current quarter isn't complete then the /3 becomes inaccurate. So if its January I would want it to divide by 1, etc. I created a column in the Date Dimension tabel MonthInQuarter. But I've been unable to levy it or any other means to have the measure divide by this dynamic number.

Thoughts?

[don_writer · Accepted solution]

Thank you so much for your input. I actually used a combination of these ideas to get to my solution. 

@Ashish_Mathur, your solution was elegant but headcount is always 1 so what this did is it took all headcounts of 1 and divided them by the dates in that quarter. So three basic dates, lets say 1/31, 2/28, & 3/31. So thats 3/3 and thus the answer for everything was 1. We needed to not just average but sum first in that quarter then divide for the average.

But it got me in the right direction.

@CheenuSing, similarly I used a variable as you suggested. I do have a Date Dimension table (Calendar table, Time Intelligence, etc). And MonthsInQtr is one of the columns. I just couldn't think how to collect it. Combining your idea of the MAX function and the DATESQTD function as the filter go me to my answer.

HeadcountQtr = 
VAR varDivisor = 
        CALCULATE(
            MAX(refDate[MonthInQtr]),
            DATESQTD(srcActive[data_as_of]))  
RETURN

    DIVIDE(
        CALCULATE(
            SUM(srcActive[headcount]),
            DATESQTD(srcActive[data_as_of])
),
    varDivisor
    )

Thank you so much!

Best regards,

~Don

[Ashish_Mathur]

Hi,

Does this work?

=CALCULATE(AVERAGE(srcActive[headcount]),DATESQTD(srcActive[data_as_of]))

[CheenuSing]

Hi @don_writer ,

I hope you have a Calendar Table (Date table) and it is connected to your fact data on date field on either table.

1.  I hope you have fields Year, QuarterinYear ( 1,2,3,4) for every (jan-Mar/Apr-Jun/Jul-Sep/Oct-Dec).

2. Create a calculated column

MonthsinQuarter =
CALCULATE (
    COUNTROWS ( VALUES ( CalendarTable[MonthOfYear] ) ),
    FILTER (
        ALL ( CalendarTable ),
        CalendarTable[Year] = EARLIER ( CalendarTable[Year] )
            && CalendarTable[QuarterOfYear] =
                                      EARLIER ( CalendarTable[QuarterOfYear] )
                 )
)

What this does is finds the number of months in a given quarter.  If in a year, say the last quarter (Sep-Dec) has only Sep month then it will be 1.  When Nov gets added it will be 2 and when Dec gets added it will be 3.

The same logic will aplly for other quarters by year.

3. 

QuarterlySalesAvg =
VAR _curQtr =
    SELECTEDVALUE ( CalendarTable[QuarterOfYear] )
VAR _CurYr =
    SELECTEDVALUE ( CalendarTable[Year] )
VAR _Divisor =
    CALCULATE (
        MAX ( CalendarTable[MonthsinQuarter] ),
        FILTER (
            ALL ( CalendarTable ),
            CalendarTable[Year] = _CurYr
                && CalendarTable[QuarterOfYear] = _curQtr
        )
    )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( FactInternetSales[SalesAmount] ),
            DATESQTD ( FactInternetSales[ShipDate] )
        ),
        _Divisor
    )

I have used a sample data called FactInternetSales ( replace this with your table name )

Replace Sales Amount, ShipDate with your field names.

When you create a Matrix Visual, with Year and Quarter number as rows and SalesAmount and QuarterlySalesAverage as values, you will get the desired results.

Based on the Year and QuarterNumber the above formula finds the MonthsinQuarter and uses that as divisor.

My sample data has only 2 months in 2008, Quartner Number 3.

The sample output

Cheers

CheenuSing