Forum Discussion

pbiandt's avatar
pbiandt
Frequent Visitor
6 years ago
Solved

Date filter month and day

Is there a way to filter just the month and day (eg. Jan 1 - Mar 23 or Feb 15-Mar 15). I have data from past 10 years and would like to look at specific date ranges within the year for each year. So ...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi pbiandt ,

     

    Sorry, I didn't fully understand you before.

    I created a calendar table and used it as a slicer, then recreated the measure to calculate the sum.

    sum_value = CALCULATE(SUM('data'[Amount]),FILTER(data,data[month_day]in VALUES(('date'[month_day]))))

    The sample pbix has been updated

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

pbiandt's avatar
pbiandt
Frequent Visitor
6 years ago

Thanks amitchandak and Greg_Deckler for your help.

 

I've tried both your syntax but I'm still not getting the desired result.

My data consists of a table with ID and Birth Date. I have a date table linked to the birth date.

 

I would like a table that displays the distinct count of ID by Birth Year filtered by a date slicer such that only the month and day are considered in the date slicer. For example, if the slicer was between Feb 15-Mar 15, then the table would display the distinct count of ID for birth dates between those dates for each year.

amitchandakIn your solution, I replaced the sum function with the distinctcount function but the filtering doesn't seem to be working.

 

Thanks V-lianl-msft for your solution. However, your solution only works if the dates were within a particular month. It wouldn't work if the dates were between months (eg. Feb 15-Mar 15).

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    pbiandt 

    Try with all

     

    measure =
var _max = format(maxx('Date','Date'[Date]),"MMDD")
var _min = format(Minx('Date','Date'[Date]),"MMDD")

return
CALCULATE(SUM(Data[Amount]), FILTER(all('Date'), format('Date'[Date],"MMDD") >=_min && format('Date'[Date],"MMDD") <=_max ))

     

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi pbiandt ,

     

    Sorry, I didn't fully understand you before.

    I created a calendar table and used it as a slicer, then recreated the measure to calculate the sum.

    sum_value = CALCULATE(SUM('data'[Amount]),FILTER(data,data[month_day]in VALUES(('date'[month_day]))))

    The sample pbix has been updated

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • V-lianl-msft's avatar
      V-lianl-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi pbiandt ,

       

      Is this problem sloved?
      If not, please let me know.

       

      Best Regards,
      Liang
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Sorry V-lianl-msft and Greg_Deckler I don't understand if the post has been solved.

         

        I downloaded V-lianl-msft file but the filter still depends on year, while pbiandt looked to filter all of his years for the time period selected. This is what I need to do, meaning I have some dates in many years (2016 to 2020) and need to filter them based just on day and month, so if I choose from January, 1st to March, 19th it selects this period from all the years I have.

         

        Maybe I didn't understand your solution, in this case please point me what I am missing.

         

        Thank you very much

         

        Nick