Conditional sum with active slicer

[gvg]

Hi,

I have a table of cash flows by date and a Calendar. The tables are related via Date field. I also have a slicer on dates. I need to sum all the values of CashFlow that appear before slicer date leaving the rest of the dates as is. I am trying to do this with this measure, however I do not know how to force it to start checking the whole table, not just the filtered one (i.e. SUMX never gets evaluated in this measure).
Would be grateful for any ideas.

Measure = if ( MAX ( Table1[Date] ) < MIN ( 'Calendar'[Date] ),
	               SUMX ( Table1, Table1[CashFlow] ),
		       MAX ( Table1[CashFlow] )
		  ) 

[Ashish_Mathur]

Hi,

I am not clear with your question.  Please share a dataset and show the expected result.

[gvg]

Here is my source and expected tables:

Source table                       Expected result table if slicer is filtered on Feb-17
Date           CashFlow            Date             CashFlow
1-Jan-16       100                 31-Mar-16        25
2-Jan-16       -40                 20-Feb-17       220
9-Mar-16       -35                 25-Feb-17       -60
20-Feb-17      220 
25-Feb-17      -60 
26-Mar-17      -30 

[v-yulgu-msft]

Hi @gvg,

I can achieve the desired result paritially using below measure. Per my knowledge, it is not possible to show only '31-Mar-16' rather than display all dates that before selected date.

As  mentioned in original post, you should remove the relationship between cashflow table and calendar table.

Measure =
IF (
    OR (
        MONTH ( MAX ( Table1[Date] ) ) < MONTH ( MAX ( 'calendar'[Date] ) ),
        AND (
            YEAR ( MAX ( Table1[Date] ) ) < YEAR ( MAX ( 'calendar'[Date] ) ),
            MONTH ( MAX ( Table1[Date] ) ) >= MONTH ( MAX ( 'calendar'[Date] ) )
        )
    ),
    CALCULATE (
        SUM ( Table1[CashFlow] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            MONTH ( Table1[Date] ) < MONTH ( MAX ( 'calendar'[Date] ) )
                || YEAR ( Table1[Date] ) < YEAR ( MAX ( 'calendar'[Date] ) )
        )
    ),
    MAX ( Table1[CashFlow] )
)

Best regards,
Yuliana Gu

[gvg]

Thanks @v-yulgu-msft. I need a solution for this my particular scenario since I have a rather big report with dozens of sheets that all rely on related date table.

[v-yulgu-msft · Accepted solution]

Hi @gvg,

Based on the measure in my original post, please create below two measures:

Max date before selected month =
CALCULATE (
    MAX ( Table1[Date] ),
    FILTER (
        ALL ( Table1 ),
        OR (
            (
                YEAR ( Table1[Date] ) = YEAR ( MAX ( 'calendar'[Date] ) )
                    && MONTH ( Table1[Date] ) < MONTH ( MAX ( 'calendar'[Date] ) )
            ),
            (
                YEAR ( Table1[Date] ) < YEAR ( MAX ( 'calendar'[Date] ) )
                    && MONTH ( Table1[Date] ) > MONTH ( MAX ( 'calendar'[Date] ) )
            )
        )
    )
)


Measure2 = IF(MAX(Table1[Date])<[Max date before selected month],BLANK(),[Measure])

Then, add [Measure2] into table visual. Then, it will only show one record where date is before the selected date.

Best regards,
Yuliana Gu

[v-yulgu-msft]

Hi @gvg,

Please remove the relationship between cashflow table and calendar table, add [date] column from calendar table to slicer. Then, create measure like below:

Measure =
CALCULATE (
    SUM ( Table1[CashFlow] ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date] <= MAX ( 'Calendar'[Date] ) )
)

Best regards,
Yuliana Gu