cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors