Forum Discussion

rtiffe's avatar
rtiffe
Frequent Visitor
2 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi rtiffe ,

 

Here's an updated version of your formula: 

MAX Per QT1-3 =
CALCULATE (
    MAXX (
        VALUES ( Reporting[Weekending_Date__c].[Date] ),
        CALCULATE (
            SUM ( Reporting[TOA__c] ),
            DATESINPERIOD (
                Reporting[Weekending_Date__c].[Date],
                MAX ( Reporting[Weekending_Date__c] ),
                -28,
                DAY
            ),
            VALUE ( Reporting[Weekending_Date__c].[Quarter] ) IN { 1, 2, 3 }
        )
    )
)

Hope it helps.

 

Best Regards,

Stephen Tao

 

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

rtiffe's avatar
rtiffe
Frequent Visitor
2 years ago

Hi,

I did what you have posted and I got the same error I keep getting. I recently changed it to 

MAXX(KEEPFILTERS(VALUES(Reporting)),
    CALCULATE( SUM('Reporting'[TOA__c]),
        FILTER(ALLSELECTED(Reporting[Weekending_Date__c]),
            DATESINPERIOD('Reporting'[Weekending_Date__c],LASTDATE('Reporting'[Weekending_Date__c]), -28, DAY))))
 
but I am getting this error "A table of multiple values was supplied where a single value was expected" I am not sure what it is referring to. Do you know ?