Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

ytd and multiple filters

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!

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

 

// 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.

View solution in original post

3 REPLIES 3
daxer-almighty
Solution Sage
Solution Sage

 

// 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
Community Champion
Community Champion

@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" }
          )
)

@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" }
)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.