Vary Chart date range

[ryand009]

Hello

I want to dynamically change the date range on a chart, depending on a selected RepPeriod in the following table

Using the following slicer

I’m testing using 2 measures

Measure #1

VAR _RepPeriod = SELECTEDVALUE ( ReportingPeriods[RepPeriod] )
        VAR _RepDate =   CALCULATE (
                MAX ( ReportingPeriods[RepDate] ),
                ReportingPeriods[RepPeriod] = _RepPeriod)
            
        RETURN
        
         _RepDate
         

Measure #1 returns the correct date (  _RepDate )

Measure #2

VAR _RepPeriod = SELECTEDVALUE ( ReportingPeriods[RepPeriod] )
        VAR _RepDate =   CALCULATE (
                MAX ( ReportingPeriods[RepDate] ),
                ReportingPeriods[RepPeriod] = _RepPeriod)
            
        RETURN

IF(
    max('Date'[Date]) >=  _RepDate && max('Date'[Date]) <= Today(),
   1,
    0
)

Measure #2 only returns 0 from the IF statement . max('Date'[Date]), is correct (the Date table is fully populated). I use it in the other measures. If I hard code a date into the IF, e.g.

IF(
    max('Date'[Date]) >=  date(2024,4,1) && max('Date'[Date]) <= Today(),
   1,
    0
)

it works as expected. I can’t understand why #Measure 2 is just returning 0 and I can’t vary the dates being passed into the IF statement????

Any suggestion, much appreciated…

Thanks in advance

Roy

[v-kpoloju-msft · Accepted solution]

Hi @ryand009,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @DataNinja777, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Relationship between both tables:

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[ryand009]

Many thanks both. 

Just what I was looking for. (I was just reaching the hair pulling out stage) 😁

[DataNinja777]

Hi @ryand009 ,

The issue in Measure #2 is due to how the CALCULATE function is being used with a Boolean condition that isn’t wrapped inside a valid filtering function like FILTER. Writing ReportingPeriods[RepPeriod] = _RepPeriod directly as a filter argument inside CALCULATE doesn’t actually apply any filtering—it just gets ignored silently. This leads to _RepDate returning a blank or incorrect value, which then causes the comparison in the IF statement to always fail.

To fix this, you need to explicitly define the row context using FILTER and ensure the table isn't already filtered by other visuals or slicers. Here's the corrected version of Measure #2:

VAR _RepPeriod = SELECTEDVALUE(ReportingPeriods[RepPeriod])
VAR _RepDate = 
    CALCULATE(
        MAX(ReportingPeriods[RepDate]),
        FILTER(
            ALL(ReportingPeriods),
            ReportingPeriods[RepPeriod] = _RepPeriod
        )
    )
RETURN
IF(
    MAX('Date'[Date]) >= _RepDate && MAX('Date'[Date]) <= TODAY(),
    1,
    0
)

This ensures the filter on RepPeriod is properly evaluated and _RepDate is correctly assigned. Once that’s fixed, the IF statement behaves as expected, returning 1 when the date is in range and 0 otherwise.

Best regards,