Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date filtering

Hi!

 

I'm relatively new to Power Bi and was having trouble with implementing a date filter. 

 

The task is to have a date filter that only show data that falls before but NOT including the selcted date. The before feature in the fiter date widget shows values on the selected date.

 

Is there a DAX command that can overide the filter and exclude values on the selected date?

 

Anything helps, thank you!!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the attached file.

    I changed a relationship type.

     

    Name measure: = 
CALCULATE (
    SELECTEDVALUE ( data[name] ),
    FILTER ( data, data[date] < MAX ( 'Calendar'[date] ) )
)

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file.

    One of ways to solve this is to have disconnected calendar table.

     

     

    Sales total: =
CALCULATE (
    SUM ( Data[Sales] ),
    FILTER ( 'Calendar', 'Calendar'[Date] < MAX ( 'Calendar slicer'[Date] ) )
)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your help!
      I just had a query, for the "Sales Total:" DAX command, how would that be altered if there wasn't anything to sum up. For example, if the chart was composed of peoples' name, date of birth or other values that are unable to be summed?

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Thank you for your feedback.

        I think you can use other function, instead of SUM, for instance, selectedvalue / min / max / and so forth.

        Please share your sample pbix file, and then more accurate solution can be written.

        Thanks.