Forum Discussion
rbeneteli
7 years agoFrequent Visitor
Sum Rows with conditions
Hello, I have a database that show each sale for each month like this one: MONTH CODE VALUE May 897 $ 100,00 May 901 $ 100,00 May 950 $ 110,00 May 999 $ 110,00 ...
- 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 ) )Best Regards,
Dale
rbeneteli
7 years agoFrequent Visitor
Yes, I already have a numeric month column
v-jiascu-msft
7 years agoMicrosoft Employee
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
)
)
Best Regards,
Dale