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 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

 

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • 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!