Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Show entire month values using slicer date filter

Hi community!. I'm preety new in Power BI and I am facing a problem with dates.

 

I need to achieve that a table shows the entire month based on the selected date by the user.

 

For example if the user selects from 07-01-2018 to 08-20-2018 I have to show in my table from 07-01-2018 until 08-31-2018.

 

Anyone can help here??

 

Attached  is the link with my model.

 

Thanks in advanced!!!

 

https://www.dropbox.com/s/11rduwn9wifdlry/test1.pbix?dl=0

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    So typically what you need to do is get your date from your slicer in a VAR using SELECTEDVALUE or MAX from your slicer. Then you want to get the MONTH from that in another VAR. Use that value to FILTER ALL of your table to where your MONTH of your date column equals your second VAR (month of your selected value from your slicer). So something like:

     

    Spoiler
    Measure = 
    VAR __selectedDate = MAX('Table'[Date])
    VAR __selectedMonth = MONTH(__selectedDate)
    RETURN
    SUMX(FILTER(ALL('Table'),MONTH('Table'[Date])=__selectedMonth),[Value])

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    So typically what you need to do is get your date from your slicer in a VAR using SELECTEDVALUE or MAX from your slicer. Then you want to get the MONTH from that in another VAR. Use that value to FILTER ALL of your table to where your MONTH of your date column equals your second VAR (month of your selected value from your slicer). So something like:

     

    Spoiler
    Measure = 
    VAR __selectedDate = MAX('Table'[Date])
    VAR __selectedMonth = MONTH(__selectedDate)
    RETURN
    SUMX(FILTER(ALL('Table'),MONTH('Table'[Date])=__selectedMonth),[Value])