Average of Previous Quarter Values

[newgirl]

Hi!

I'm currently building this report that shows client count of sales. By definition, a client is counted as '1' if they have volume.

I have 2 pages in the report -  one to show the monthly client count and the other page to show comparisons of current month with previous month, quarterly average, and target.

So in page 1, I have this measure and this is how I present it in the matrix.

ClientCount_Try2 =
SUMX (
    FILTER (
        SUMMARIZE (
            'CR tbl_BillingReport',
            CatSapJobSite[ComercialManager],
            CatSapJobSite[AreaSalesManager],
            'CR tbl_BillingReport'[Sold to Partner],
            "_1", CALCULATE ( DISTINCTCOUNT ( 'CR tbl_BillingReport'[Sold to Partner] ) )
        ),
        [Volume] > 0
    ),
    [_1]
)

For the 2nd page, I have a date slicer (in which I will make it a 'Today' slicer) and in this page, it will compare the MTD with the previous month, the ave. of the previous quarter and the target.

And what I'm having a hard time of is creating the measure for the ave. of the previous quarter values.

This is how it's computed in Excel:

So in my case, for example, I filter the page as June 30, so the previous quarter values it should capture is Jan to Mar.

How should the formula for the DAX be?

Hope somebody can help me

[amitchandak]

@newgirl , You have to create a measure like

Last QTD Sales = CALCULATE(Averagex(Values(Date[Month year]) , calculate(SUM(Sales[Sales Amount]))) ,DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

The first part forces the monthly sum and then Avg

[newgirl]

Hi @amitchandak 

I tried the measure you provided but just changed the 2nd CALCULATE portion since it looks like you're getting the average of the sales but in my case, I need the average of the monthly values of client count.

Here is the final measure I did:

Ave_QTD =
CALCULATE (
    AVERAGEX ( VALUES ( 'Calendar'[MonthYear] ), [ClientCount_Try2] ),
    DATESQTD ( DATEADD ( 'Calendar'[Date], -1, QUARTER ) )
)

and here are the values showing:

I want to validate the values that are showing so in my page 1 of monthly client count, so  I downloaded it and computed manually the average of the monthly values.

Monthly values from Page 1Comparison of manual computation versus Ave_QTD

fyi that the measure I used in page 1 is below.

ClientCount_Try2 =
SUMX (
    FILTER (
        SUMMARIZE (
            'CR tbl_BillingReport',
            CatSapJobSite[ComercialManager],
            CatSapJobSite[AreaSalesManager],
            'CR tbl_BillingReport'[Sold to Partner],
            "_1", CALCULATE ( DISTINCTCOUNT ( 'CR tbl_BillingReport'[Sold to Partner] ) )
        ),
        [Volume] > 0
    ),
    [_1]
)

How else to tweak the new measure of Ave_QTD since there are some discrepancies showing?

[newgirl · Accepted solution]

Hi @amitchandak !

After some searching and tweaking, I changed the DATEADD to PARALLELPERIOD, as suggested in one of the Reddit threads I found. 

In the final measure, this is how I modified it. Posting it here for others' reference.

Ave_QTD = CALCULATE(AVERAGEX(VALUES('Calendar'[MonthYear]),[ClientCount_Try2]),DATESQTD(PARALLELPERIOD('Calendar'[Date],-1,QUARTER)))