Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.
// 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.
@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" }
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
10 | |
10 | |
9 | |
6 |