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.

 

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.
Need Help

13 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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-Forum/ba-p/963216
    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/1447523

  • 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 ?
    • rtiffe's avatar
      rtiffe
      Frequent Visitor
      2 years ago
      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?
  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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's avatar
      rtiffe
      Frequent Visitor
      2 years ago

      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 DateTOA
      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's avatar
    Syndicate_Admin
    Administrator
    2 years ago
    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's avatar
      rtiffe
      Frequent Visitor
      2 years ago

      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.

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