Forum Discussion

julsr's avatar
julsr
Icon for Continued Contributor rankContinued Contributor
1 year ago

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

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 ...
Need Help
Show and Tell
Tips and Tricks
rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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's avatar
    julsr
    Icon for Continued Contributor rankContinued Contributor
    1 year ago

    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
        )
    )
  • julsr's avatar
    julsr
    Icon for Continued Contributor rankContinued Contributor
    1 year ago

    Hi everyone! Any help on this would be appreciated. Thank you!