Forum Discussion
Average quarter data by month
sssssssssssssss
I think I got it.
This is the measure:
Avg by Q = CALCULATE( SUM('Quarter'[USD Net Rev])/3, ALLEXCEPT('FiscalYMD', 'FiscalYMD'[Year Quarter]), VALUES(FiscalYMD[Year Quarter] ) )I also added two columns Year Month and Year Quarter to your date table:
Year Month = FiscalYMD[Year] & "-" & format(FiscalYMD[Month], "00")
Year Quarter = FiscalYMD[Year] & " Q" & FiscalYMD[Quarter]Then it looks like this:
Because I use VALUES of the quarters in the measure you can leave the quarters out of the table.
Here is the link to your file.
It works with me...
Hi siwyan_1,
Can you please mark my post as the solution?
(Using a ; or , depends on your regional settings in windows. )
Thanks, Edgar
Hi Edgar,
This still does not solve my problem.
This is what I put into the measure: Avg by Q = CALCULATE(sum('Retail_Quarter'[USD Net Rev])/3,ALL('CalendarMonthYear'[MonthBeginID]))
The averaged data (right table) does not fill into all monthes on left table. e.g. right table Month 201507 average is 791,697; this number only showed up on left table for month 201507. The 201508 and 201509 do not have any number fill in.
I've been hitting my head against the wall the whole morning and trying to get it solved. but still no solution. Help please.
The difference with my example is that I have a date dimension table, which links the months to quarters.
Do you have that as well? It's highly recommended.