Filtering date hierarchies to stop running totals till a year and qtr

[andy192]

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

[DataNinja777 · Accepted solution]

Hi @andy192 ,

The issue arises because SELECTEDVALUE('table1'[DateStamp]) does not always return a single date at the Year-Quarter level of aggregation. Since DateStamp is at the daily granularity, when multiple dates exist in a quarter, SELECTEDVALUE may return BLANK() or unexpected results. To resolve this, you should retrieve the latest date within each quarter and compare it against the cutoff date.

One way to do this is by using MAX('table1'[DateStamp]), which ensures that the latest date within the quarter is evaluated. This way, the comparison correctly filters out rows beyond the specified cutoff.

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

Another alternative is to use LASTDATE('table1'[DateStamp]), which explicitly retrieves the last available date in the current filter context.

Ratio =
VAR LatestDate = LASTDATE('table1'[DateStamp])
RETURN
IF ( 
    LatestDate <= DATE(2024, 9, 30),
    DIVIDE(
        ('table1'[A] + 'table1'[B]),
        'table1'[C],
        0
    )
)

By applying this approach, the unwanted row (2024 Q4 1.4) should no longer appear, as the last date for each quarter is properly evaluated before performing the calculation.

Best regards,

[Punithurs]

Hi @andy192,

YourMeasure =    
 CALCULATE(
    DIVIDE(
        (SUm(sales_data[SalesAmount]) + RunningB),
        RunningC
    ),FILTER('Calendar','Calendar'[Date] <= DATE(2024, 9, 30)))

[Punithurs]

Hi @andy192 ,
Try this out, 
The below measure will add filter context to all the measures being used. 
If condition will not stop/add filter context to the parameters in the divide function. 

YourMeasure =     
CALCULATE(

    DIVIDE(

        (Sum(sales_data[Quantity]) + RunningB),

        RunningC,

        0

    ),FILTER('Calendar','Calendar'[Date] <= DATE(2024, 9, 30)))

[Punithurs · Accepted solution]

Hi @andy192 ,

Just replace your measure with the below. 
This should work. 
The divide function is unable to determine at what date it should stop calculating, 
Where if condition only checks whether the given condition is satisfied or not but doesnt add to filter context.

 
Yourmeasure = 

    CALCULATE(

    DIVIDE(

        (Sum(sales_data[Quantity]) + RunningB),

        RunningC,

        0

    ),FILTER('Calendar','Calendar'[Date] <= DATE(2024, 9, 30)))

[DataNinja777 · Accepted solution]

Hi @andy192 ,

The issue arises because SELECTEDVALUE('table1'[DateStamp]) does not always return a single date at the Year-Quarter level of aggregation. Since DateStamp is at the daily granularity, when multiple dates exist in a quarter, SELECTEDVALUE may return BLANK() or unexpected results. To resolve this, you should retrieve the latest date within each quarter and compare it against the cutoff date.

One way to do this is by using MAX('table1'[DateStamp]), which ensures that the latest date within the quarter is evaluated. This way, the comparison correctly filters out rows beyond the specified cutoff.

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

Another alternative is to use LASTDATE('table1'[DateStamp]), which explicitly retrieves the last available date in the current filter context.

Ratio =
VAR LatestDate = LASTDATE('table1'[DateStamp])
RETURN
IF ( 
    LatestDate <= DATE(2024, 9, 30),
    DIVIDE(
        ('table1'[A] + 'table1'[B]),
        'table1'[C],
        0
    )
)

By applying this approach, the unwanted row (2024 Q4 1.4) should no longer appear, as the last date for each quarter is properly evaluated before performing the calculation.

Best regards,