Trying to calculate Quarterly average with Month data

[afarbod]

Hi Team, I am new on this, need your help?

I have a table that has patient count on Y axis and Year/month on X axis.   I need to add Quarterly patient count averaging the 3 months in each quarter.   How do I add that to my table?

[afarbod]

Thanks you again, this forum is amazing,  I am still struggling.  Let me give you more data.   I have two tables, in Table 1, I have total patient counts and it is mapped as follow, now I am trying to use this information to calculate average quarterly patien count

Table 2 is the date Table, and I have the following setup 

Table_DateTable =

ADDCOLUMNS (

CALENDAR (DATE(2021,4,1), DATE(2035,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthShortYear", FORMAT ( [Date], "mmm YYYY"),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfMonth", DAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),

"TreatmentMonth", DATE(YEAR([Date]),MONTH([Date]),1)

//"RelativeMonthsAgo", datediff([Date], TODAY(), MONTH)

)

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Anonymous]

Hi @afarbod 

Please provide your sample data in a copyable format instead of screenshots. If you are not sure how to share the data, please refer to the following post: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @afarbod 

If you want to calculate the value every three months on a rolling basis, you can consider the method provided by Shravan133 .

If you want to calculate the average for each quarter, please try the following dax:

Quarterly Avg  = 
var _year = YEAR(SELECTEDVALUE('Table'[Date]))
VAR _quarter = QUARTER(SELECTEDVALUE('Table'[Date]))
RETURN
AVERAGEX(FILTER(ALL('Table'),YEAR('Table'[Date]) = _year && QUARTER('Table'[Date]) = _quarter),'Table'[value])

 Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[afarbod]

Hello Jayleny, I will try your suggestion and see if it works, Thank you so much for your help

[afarbod]

meant to say DATESQTD

[Shravan133]

Yeah try using DATESQTD()

or 

try this alternative to get a rolling 3 month average:

Quarterly Avg Patient Count =
CALCULATE(
AVERAGE('YourTable'[Patient Count]),
DATESINPERIOD(
'DateTable'[Date],
MAX('DateTable'[Date]),
-3,
MONTH
)
)

[afarbod]

Thank you,  I added the formula, I got this error. Failed to resolve name 'DATESQTR'. It is not a valid table, variable, or function name.   Is the SATESQTD an option that I can use?

[Shravan133]

go to the table where you have the patient count data and create a new measure to calculate the quarterly average:

Quarterly Avg Patient Count =

CALCULATE( AVERAGE('YourTable'[Patient Count]), DATESQTR(DATEADD('DateTable'[Date], 0, MONTH)) )