Forum Discussion

rbeneteli's avatar
rbeneteli
Frequent Visitor
7 years ago
Solved

Sum Rows with conditions

Hello,

 

I have a database that show each sale for each month like this one:

 

MONTHCODEVALUE
May897 $ 100,00
May901 $ 100,00
May950 $ 110,00
May999 $ 110,00
May1001 $ 100,00
May1003 $ 100,00
June910 $ 100,00
June955 $ 100,00
June960 $ 100,00
June1101 $ 110,00
June1125 $ 110,00
June1132 $ 110,00
June1152 $ 100,00
July953 $ 110,00
July960 $ 100,00
July1101 $ 100,00
July1130 $ 100,00
July1132 $ 100,00
July1152 $ 110,00
July1153 $ 110,00
August955 $ 100,00
August960 $ 100,00
August1101 $ 110,00
August1102 $ 100,00
August1140 $ 100,00
August1180 $ 110,00
August1230 $ 100,00
August1225 $ 100,00

 

My main task is to sum the values of sale in each month, but i have some conditions, when a I look for August as example, I need first to look at the code of that sale, if this code is greater then the base code from July it counts as a sale. But if it is greater then the code of June it will count as a sale only if it have occurred in July, here is the other table that show the base codes. If the code is smaller than June it will not count as sale in August.

 

MONTH bCODE
May950
June1101
July1132
August1187

 

Analyzing the first data that i have shown I did manually the analysis for july and august, and I should sum the rows that have a number 1 in the new column as it follow:

 

MONTHCODEVALUE 
May897 $ 100,00 
May901 $ 100,00 
May950 $ 110,00 
May999 $ 110,00 
May1001 $ 100,00 
May1003 $ 100,00 
June910 $ 100,00 
June955 $ 100,00 
June960 $ 100,00 
June1101 $ 110,00 
June1125 $ 110,00 
June1132 $ 110,00 
June1152 $ 100,00 
July953 $ 110,000
July960 $ 100,001
July1101 $ 100,001
July1130 $ 100,001
July1132 $ 100,001
July1152 $ 110,001
July1153 $ 110,001
August955 $ 100,000
August960 $ 100,000
August1101 $ 110,001
August1102 $ 100,000
August1140 $ 100,001
August1180 $ 110,001
August1230 $ 100,001
August1225 $ 100,001

 

So I can get a visual like that one in PBI:

 

MonthValue
Januaryx
Februaryx
Marchx
Aprilx
Mayx
Junex
July620
August520
Septemberx
Octoberx
Novemberx
Decemberx

 

Just to clarify, when I was analysing December I should look for Code greater than November, or greater than October but that happened in November.

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi rbeneteli,

     

    Please check out the demo in the attachment. I believe you have a date table. The [Measure 2] is only for test purpose. 

    Measure =
VAR lastMonthCode =
    CALCULATE ( MIN ( 'Table2'[bCODE] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
VAR last2Monthcode =
    CALCULATE (
        MIN ( 'Table2'[bCODE] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MAX ( 'Calendar'[Date] ), -2 ),
            -1,
            MONTH
        )
    )
VAR last2MonthCodes =
    CALCULATETABLE (
        VALUES ( Table1[CODE] ),
        PREVIOUSMONTH ( 'Calendar'[Date] ),
        'Table1'[CODE] >= last2Monthcode
    )
RETURN
    CALCULATE (
        SUM ( Table1[VALUE] ),
        FILTER (
            'Table1',
            'Table1'[CODE] >= lastMonthCode
                || Table1[CODE] IN last2MonthCodes
        )
    )

    sum_rows_with_conditions

    Best Regards,
    Dale

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    This will be far easier if you have a numeric month sort column, do you have one of those or are you able to add one in your query?

    • rbeneteli's avatar
      rbeneteli
      Frequent Visitor
      7 years ago
      Yes, I already have a numeric month column
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi rbeneteli,

         

        Please check out the demo in the attachment. I believe you have a date table. The [Measure 2] is only for test purpose. 

        Measure =
VAR lastMonthCode =
    CALCULATE ( MIN ( 'Table2'[bCODE] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
VAR last2Monthcode =
    CALCULATE (
        MIN ( 'Table2'[bCODE] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MAX ( 'Calendar'[Date] ), -2 ),
            -1,
            MONTH
        )
    )
VAR last2MonthCodes =
    CALCULATETABLE (
        VALUES ( Table1[CODE] ),
        PREVIOUSMONTH ( 'Calendar'[Date] ),
        'Table1'[CODE] >= last2Monthcode
    )
RETURN
    CALCULATE (
        SUM ( Table1[VALUE] ),
        FILTER (
            'Table1',
            'Table1'[CODE] >= lastMonthCode
                || Table1[CODE] IN last2MonthCodes
        )
    )

        sum_rows_with_conditions

        Best Regards,
        Dale