Forum Discussion
Creating a new measure
Hi, I am trying to find the max sum of TOA within the first, second and third quarter of the last 4 weeks of each month per quarter. For Quarter four I just need the max sum of TOA for the quarter...
the last 4 weeks in Q1does that include April 1 and April 2 ?
I did this
TOA__c max per Weekending_Date__c =
MAXX(
KEEPFILTERS(VALUES('Reporting'[Weekending_Date__c])),
CALCULATE(SUM('Reporting'[TOA__c]))
)
and it works but I need only the last 4 weeks of the quarter not the entire quarter. So I tried adding the - 28 days which looks like this but it gives me HUGE numbers such as 44,105 and 25k etc.
MAXX(
KEEPFILTERS(VALUES('Reporting'[Weekending_Date__c])),
CALCULATE(SUM('Reporting'[TOA__c]),
FILTER(
ALL('Reporting'[Weekending_Date__c]),
'Reporting'[Weekending_Date__c]
>=('Reporting'[Weekending_Date__c]) - 27
&& 'Reporting'[Weekending_Date__c] <= ('Reporting'[Weekending_Date__c])
)
)
)