ytd and multiple filters

[Anonymous]

Hello everyone,

I'd like to get a YTD value and have a month slicer ('Date'-Table) in my report.

Following measure is showing me the value of the month selected in month-slicer at the moment, but not the ytd value up to the selected month as it should

measure (IST;YTD) =
    CALCULATE(
          SUM('table1'[value]);
          DATESYTD('Date'[Date]);
          FILTER(
                'table1';
                'table1'[column1] IN { "ABC" } &&
                'table1'[column2] IN { "XYZ" } &&
                RELATED('table2'[column5]) IN { "DEF" }
          )
)

As I understand the FILTER function in between DATESYTD is the reason that it's not working as as I expected.

Does anyone have a hint for me, how to make this measure working correctly?

Thank you very much in advance!

[daxer-almighty · Accepted solution]

// For this to work, 'Date' must 
// be a proper Date table marked
// as such in the model.

(IST;YTD) =
CALCULATE(
    SUM( 'table1'[value] ),
    KEEPFILTERS( 'table1'[column1] = "ABC" ),
    KEEPFILTERS( 'table1'[column2] = "XYZ" ),
    KEEPFILTERS( 'table2'[column5] = "DEF" ),
    DATESYTD( 'Date'[Date] )
)

If this does not do what you want, you can try to remove KEEPFILTERS around the equalities (but leave the equalities intact).

And please remember that you should never filter a full table if you can filter a single column. NEVER.

[themistoklis]

@Anonymous 

Try the following formula:

Measure =
CALCULATE (
    CALCULATE (
        SUM('table1'[value]);
        DATESYTD('Date'[Date])
    ),
          FILTER(
                'table1';
                'table1'[column1] IN { "ABC" } &&
                'table1'[column2] IN { "XYZ" } &&
                RELATED('table2'[column5]) IN { "DEF" }
          )
)

[amitchandak]

@themistoklis , Try like

measure (IST;YTD) =
CALCULATE(
SUM('table1'[value]);
DATESYTD('Date'[Date]);
FILTER(
'table1';
'table1'[column1] IN { "ABC" } &&
'table1'[column2] IN { "XYZ" } &&

), 'table2'[column5] IN { "DEF" }
)