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, thank you for reading and responding to my post. I am still getting the same error. "Function "CONTAINSROW" does not support comparing values of type integrer with values of data Text, Consider using the VALUE or FORMAT function to conver one of the values."

     

    The Weekending_Date__c is a date data type and the TOA_c is a decimal number data type so not sure why that error is stating that?

  • 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 ?