cancel
Showing results 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

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

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

1 ACCEPTED SOLUTION
Helper V

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

3 REPLIES 3
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

Helper V

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?

Helper V

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.