Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Auto-Filter Tables Based on Date in Column

I am still relatively new to Power BI, so please forgive me if this is a simple question; I have been looking for a solution for days and haven't been able to find an answer. This is also my first ti...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous ,

     

    You can modify the paycode at underlined section and create a new measure.

    I have modified the demo, please try it:demo.pbix

     

    Best Regards,

    Link

v-xulin-mstf's avatar
v-xulin-mstf
Community Support
5 years ago

Hi, Anonymous 

I'm not clear your expected outcome, please details.
For example, you want to get the count of days that Mosby, Ted leave of absence, and the date must between date started and date ended.
In addition, i'm not clear the reopened date mean, please details.

Best Regards,

Link

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Basically, I would like the report to auto filter to only count the leaves of absence AFTER the start date of the Corrective Action OR after the Reopen date if there is one. 


The reopen date just shows the last time I extended the Corrective ACtion. I'm not sure any of my sample data actually has any, but for example if I have to extend Mosby, Ted's Corrective ACtion by 10 days, the Reopen date will show when I did that and the reopen reason would note something like "Extendind due to leave of absence - Original End: (original end date), New End (End date after extension).

 

This is so I know I have already looked at the leaves of absence up to that date for the individual and know not to double count the dates before this.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi Anonymous 
    Create a measure as:

    Measure = var _table=SELECTCOLUMNS(CAs,"Employee ID",CAs[Employee ID],"Date started",IF(CAs[Reopened Date]=BLANK(),CAs[Date started],CAs[Reopened Date])) 
return
var _table2=FILTER(GENERATE(_table,LoAs),[Employee ID]=[ID:]&&[Date]>=[Date started]&&[Pay Code]="Leave of Absence")
return
COUNTAX(_table2,[Employee ID])

    Here is the demo, please try it: Auto-Filter Tables Based on Date in Column

    Best Regards,

    Link

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      What should I do with the measure? In your example, the card visual that has the measure is returning blank.

      • v-xulin-mstf's avatar
        v-xulin-mstf
        Community Support
        5 years ago

        Hi Anonymous 

        The measure return the count the leaves of absence after the start date of the corrective action or after the reopen date if there is one.

        The card visual that has the measure is returning blank because there have no data that meets the conditions.

        Please try the demo and check if it works.

        Best Regards,

        Link