Stop running queries on date year and quarter

[andy421]

I have a date hierarchy from which I have selected 2 columns Year and Quarter.
I got 3 measures 2 of which give data at the month level and one only at the quarter level. So I am combining these 3 measures
and performing a calculation to show data in a table visual in Power BI

I have a ratio like Ratio=A+B/C
B and C are running totals and A is a value at a point in time

Year Quarter Ratio
2021 4 0.1
2022 1 0.2
2022 2 0.21
2022 3 0.3
2022 4 0.28
2023 1 0.5
2023 2 0.6
2023 3 0.6
2023 4 0.6
2024 1 1.2
2024 2 1.3
2024 3 1.2
2024 4 0.65

Year and Quarter are displayed from a DateStamp column by dragging the date hierarchy part and removing the months and days.

if I try something like
Ratio =
IF (SELECTEDVALUE('table1'[DateStamp]) <=DATE(2024,9,30),
DIVIDE(
('table1'[A] +
'table1'[B]),
'table1'[C],
0
)
)

I am not expecting the last row to be displayed when I drag the ratio to a table visual
But still this row is displayed
2024 4 1.4

if I change to
if I try something like
Ratio =
IF (SELECTEDVALUE('table1'[DateStamp]) <=DATE(2022,1,20),
DIVIDE(
('table1'[A] +
'table1'[B]),
'table1'[C],
0
)
)

then I get output

Year Quarter Ratio
2021 4 0.1
2022 1 0.2
2023 1 0.5
2023 3 0.6
2024 2 1.3
2024 3 1.2
2024 4 0.65

So clearly the filtering doesn't seem to work on date and I am not sure why as DateStamp is of Date data type. Also tried Date/Time data type but no luck

[bhanu_gautam · Accepted solution]

@andy421 , Try using

DAX
Ratio =
CALCULATE (
IF (
MAX('table1'[DateStamp]) <= DATE(2024, 9, 30),
DIVIDE(
('table1'[A] + 'table1'[B]),
'table1'[C],
0
)
)
)