Creating a new measure

[rtiffe]

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.

So if I have TOA in Jan(15) Feb(12) March(8) April(10) = the number I would need to capture is 10 because it is the highest number of the last 4 weeks of the quarter.

Is this formula possible when I try to make a new measure? 

I did write this up 

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 ), Reporting[Weekending_Date__c].[Quarter] IN {1,2,3} )))

but it is giving me an error stating CONTAINSROW does not support comparing values of type integer with values of type Text.

[Syndicate_Admin]

the last 4 weeks in Q1

does that include April 1 and April 2 ?

[rtiffe]

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

        )

    )

)

[rtiffe]

yes

[Syndicate_Admin]

The Weekending_Date__c is a date data type

looks more like a text type to me.

the last 4 weeks of each month per quarter. 

Please be more specific. Does this include partial weeks? What is your week numbering methodology?  Weeks and months are not compatible.  When do your quarters start?

Ideally all of these questions should be answered by an external calendar table that clearly marks the weeks in the month, the weeks in the quarter, and then weeks in the year etc.

[rtiffe]

I edited the Weekending Date to a short date so in the power query the column data is in short date format (3-15-2022) so it is not a text type.

q1 = jan - march

q2 = april-june

q3 = july-sept

q4=oct- dec

So for Q1 the number I need to calculate would be the max TOA number of the last 4 weeks in Q1. So by looking at the excel file the answer would be 3486.

[Syndicate_Admin]

Maybe DATESINPERIOD somehow translates to CONTAINSROW.

Generally you want to avoid using the auto date/time hierarchies.   Any chance you can post a sample .pbix that illustrates the issue?

[rtiffe]

In my visualization I have the Weekend Date show year and quarter and the Y axis i am trying to have it be this new measure I am trying to create.

Weekend Date	TOA
12/26/2021 - 1/1/2022	2,148
1/2/2022 - 1/8/2022	2,320
1/9/2022 - 1/15/2022	2,516
1/16/2022 - 1/22/2022	2,700
1/23/2022 - 1/29/2022	2,838
1/30/2022 - 2/5/2022	2,909
2/6/2022 - 2/12/2022	3,030
2/13/2022 - 2/19/2022	3,137
2/20/2022 - 2/26/2022	3,200
2/27/2022 - 3/5/2022	3,300
3/6/2022 - 3/12/2022	3,353
3/13/2022 - 3/19/2022	3,410
3/20/2022 - 3/26/2022	3,486
3/27/2022 - 4/2/2022	3,552
4/3/2022 - 4/9/2022	3,506
4/10/2022 - 4/16/2022	3,482
4/17/2022 - 4/23/2022	3,446
4/24/2022 - 4/30/2022	3,457
5/1/2022 - 5/7/2022	3,388
5/8/2022 - 5/14/2022	3,387
5/15/2022 - 5/21/2022	3,341
5/22/2022 - 5/28/2022	3,266
5/29/2022 - 6/4/2022	3,203
6/5/2022 - 6/11/2022	3,203
6/12/2022 - 6/18/2022	3,192
6/19/2022 - 6/25/2022	3,185
6/26/2022 - 7/2/2022	3,206
7/3/2022 - 7/9/2022	3,115
7/10/2022 - 7/16/2022	3,133
7/17/2022 - 7/23/2022	3,138
7/24/2022 - 7/30/2022	3,170
7/31/2022 - 8/6/2022	3,179
8/7/2022 - 8/13/2022	3,162
8/14/2022 - 8/20/2022	3,194
8/21/2022 - 8/27/2022	3,215
8/28/2022 - 9/3/2022	3,255
9/4/2022 - 9/10/2022	3,228
9/11/2022 - 9/17/2022	3,306
9/18/2022 - 9/24/2022	3,334
9/25/2022 - 10/1/2022	3,387
10/2/2022 - 10/8/2022	3,420
10/9/2022 - 10/15/2022	3,437
10/16/2022 - 10/22/2022	3,496
10/23/2022 - 10/29/2022	3,541
10/30/2022 - 11/5/2022	3,561
11/6/2022 - 11/12/2022	3,579
11/13/2022 - 11/19/2022	3,598
11/20/2022 - 11/26/2022	3,499
11/27/2022 - 12/3/2022	3,532
12/4/2022 - 12/10/2022	3,511
12/11/2022 - 12/17/2022	3,453
12/18/2022 - 12/24/2022	3,234
12/25/2022 - 12/31/2022	3,034
1/1/2023 - 1/7/2023	3,169
1/8/2023 - 1/14/2023	3,430
1/15/2023 - 1/21/2023	3,543
1/22/2023 - 1/28/2023	3,638
1/29/2023 - 2/4/2023	3,688
2/5/2023 - 2/11/2023	3,729
2/12/2023 - 2/18/2023	3,764
2/19/2023 - 2/25/2023	3,812
2/26/2023 - 3/4/2023	3,859
3/5/2023 - 3/11/2023	3,825
3/12/2023 - 3/18/2023	3,843
3/19/2023 - 3/25/2023	3,805
3/26/2023 - 4/1/2023	3,856
4/2/2023 - 4/8/2023	3,796
4/9/2023 - 4/15/2023	3,734
4/16/2023 - 4/22/2023	3,663
4/23/2023 - 4/29/2023	3,600
4/30/2023 - 5/6/2023	3,565
5/7/2023 - 5/13/2023	3,509
5/14/2023 - 5/20/2023	3,466
5/21/2023 - 5/27/2023	3,434
5/28/2023 - 6/3/2023	3,389
6/4/2023 - 6/10/2023	3,351
6/11/2023 - 6/17/2023	3,325
6/18/2023 - 6/24/2023	3,312
6/25/2023 - 7/1/2023	3,322
7/2/2023 - 7/8/2023	3,162
7/9/2023 - 7/15/2023	3,213
7/16/2023 - 7/22/2023	3,222
7/23/2023 - 7/29/2023	3,206
7/30/2023 - 8/5/2023	3,172
8/6/2023 - 8/12/2023	3,147
8/13/2023 - 8/19/2023	3,123
8/20/2023 - 8/26/2023	3,152
8/27/2023 - 9/2/2023	3,159
9/3/2023 - 9/9/2023	3,081
9/10/2023 - 9/16/2023	3,125
9/17/2023 - 9/23/2023	3,132
9/24/2023 - 9/30/2023	3,142

[Syndicate_Admin]

You have not mentioned CONTAINSROW before. Please show your DAX query.

[rtiffe]

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

I do not have CONTAINSROW in my formual so that is why im confused?

[Anonymous]

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]

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 ?

[rtiffe]

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?

[Syndicate_Admin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...