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
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
the last 4 weeks in Q1

does that include April 1 and April 2 ?

  • rtiffe's avatar
    rtiffe
    Frequent Visitor
    2 years ago

    yes

  • rtiffe's avatar
    rtiffe
    Frequent Visitor
    2 years ago

    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])
            )
        )
    )