cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors