Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
newgirl
Helper V
Helper V

Average of Previous Quarter Values

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]
)

ave2.JPG

 

 

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:

ave3.JPG

 

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.

 

ave4.JPGave5.jpg

 

How should the formula for the DAX be?

 

Hope somebody can help me

1 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)))

 

 

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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

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:

ave6.JPG

 

 

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 1Monthly values from Page 1Comparison of manual computation versus Ave_QTDComparison 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?

 

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)))

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors