Measure is not returning any value but working when explicit value is being used

[julsr]

Hi everyone, 

I have a code that uses the D-1 feature (or picks the selected date by the user), both commented here and it works perfectly when I use it in a table or visualization with the date explicitly added. However, what I need is to return the value (date) that meets this criteria to use it as a filter in multiple measures. How can I do this?

Please find here the PBIX file with test data and the "check_right_with_explicit_date" measure is the one that works with the dates added.

Thanks

[rajendraongole1]

Hi @julsr - you need to create a measure or calculated column that dynamically determines the date. Then, you can use this dynamic date as a filter context for other calculations.

create a ameasure 

SelectedDate =
VAR MaxDate = MAX('DateTable'[Date]) // Adjust this to match your logic
RETURN
IF(
MaxDate = TODAY() - 1, // Replace with your D-1 logic
MaxDate,
BLANK()
)

Once you have the measure, you can use it as part of your filter logic in other measures.

FilteredMeasure =
CALCULATE(
SUM('FactTable'[Value]),
FILTER(
'DateTable',
'DateTable'[Date] = [SelectedDate]
)
)

If your SelectedDate measure is returning a scalar value (like a specific date), you can directly apply it as a filter condition in your visualizations.

[julsr]

Thanks for your answer. That's exactly what I've done in my PBIX which I attached in the thread. The problem is that the formula is not returning a value that I can use in another measure. For example, what I want/need is to return the DayMinus1 or SelectedDate (the value itself) and then use this result in another measure as a filter, but it's not working and I don't know what's wrong.

check_3 =

VAR table1 = ALL('dummy-sales-data'[Fecha])

VAR table2 = ALLSELECTED('dummy-sales-data'[Fecha])

VAR SelectedDate = CALCULATE(

    MIN('dummy-sales-data'[Fecha]),

    ALLSELECTED('dummy-sales-data'[Fecha])

)

VAR test = IF(

    COUNTROWS(EXCEPT(table1, table2)) = 0

    && COUNTROWS(EXCEPT(table2, table1)) = 0,

    TRUE,

    FALSE

)

VAR MaxDate = CALCULATE(MAX('dummy-sales-data'[Fecha]), ALL('dummy-sales-data'))

VAR DayMinus1 = CALCULATE(

    MAX('dummy-sales-data'[Fecha]),

    FILTER(

        ALL('dummy-sales-data'),

        'dummy-sales-data'[Fecha] < MaxDate

    )

)

RETURN

IF(

    test,

    // When all slicers are clear - return the D-1 date

    IF(

        MAX('dummy-sales-data'[Fecha]) = DayMinus1,

        DayMinus1,  // Return D-1 date

        MaxDate     // Return max date if not D-1

    ),

    // When a date is selected - return the selected date

    IF(

        MAX('dummy-sales-data'[Fecha]) = SelectedDate,

        SelectedDate,  // Return selected date

        MaxDate       // Return max date if not selected date

    )

)