Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a dynamic date filter for the current day

Hi!

 

I am having trouble with the date filter I have created. It is dynamically made with DAX commands to filter data according to the current date.

 

I have attached a sample power bi file of this filter. The issue I am having is I would like the filter to only show the data with dates that occur prior to and including the selected date. 

 

https://drive.google.com/file/d/1dJQOgVHnbxpcQ3QbJ3qdMbxajMPoWsHI/view?usp=sharing

 

Thank you for helping!!

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    You could create a flag measure.

    flag = 
var _max=CALCULATE(MAX('data'[date]),ALL('data'))
var _year= IF(MAX('FilterYear_Table'[Disp Year])="Today’s Year",YEAR(_max), CONVERT(MAX('FilterYear_Table'[Disp Year]),INTEGER))
var _month= IF(MAX('FilterMonth_Table'[Disp Month])="Today’s Month",MONTH(_max),MONTH(  MAX('FilterMonth_Table'[Disp Month]) & " 1"))
var _day= IF(MAX('FilterDay_Table'[Disp Day])="Today’s Day",DAY(_max),CONVERT(MAX('FilterDay_Table'[Disp Day]),INTEGER))
return IF(MAX([date])<=DATE(_year,_month,_day),1,0)

    Then apply it into filter.

    The final output is shown below:

    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous ;

    You could create a flag measure.

    flag = 
var _max=CALCULATE(MAX('data'[date]),ALL('data'))
var _year= IF(MAX('FilterYear_Table'[Disp Year])="Today’s Year",YEAR(_max), CONVERT(MAX('FilterYear_Table'[Disp Year]),INTEGER))
var _month= IF(MAX('FilterMonth_Table'[Disp Month])="Today’s Month",MONTH(_max),MONTH(  MAX('FilterMonth_Table'[Disp Month]) & " 1"))
var _day= IF(MAX('FilterDay_Table'[Disp Day])="Today’s Day",DAY(_max),CONVERT(MAX('FilterDay_Table'[Disp Day]),INTEGER))
return IF(MAX([date])<=DATE(_year,_month,_day),1,0)

    Then apply it into filter.

    The final output is shown below:

    Best Regards,
    Community Support Team _ Yalan Wu
    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
      4 years ago

      Hi,

      Thank you for your help! I was wondering if how would you create a card that would display the total number of names displayed in the chart for the selected date? 

      When I tried duplicating the chart and coverting the data into a card, the card only seems to display the overall total number of data values instead of the number of data values displayed.

       

      Thank you!

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

    Anonymous , You have create these in you table 

     

    Year Type = Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )

     

    Month Type = Switch( True(),
    Eomonth([Date],0)= Eomonth(Today(),0),"This Month" ,
    month([Date]) & "" // or// format([Date],"mmm")
    )

     

     

    Month Type = Switch( True(),
    [Date]= Today(),"This Day" ,
    Day([Date]) & ""
    )

     

    Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi! Thank you for your help! I was wondering if there was a way for me to also display all the data that falls on the dates leading up to the selected date as well?