Forum Discussion
Auto-Filter Tables Based on Date in Column
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
Hi Anonymous
I wonder if i get your expected outcome or not.
For example, Mosby, Ted leaves of absence after date started or reopen date if there is one.Then count the days of leave of absence.
If it has mistakes, please show me the right.
Could you share the sample data on format of excel?
Expected output would help tremendously.
Best Regards,
Link
I have updated the excel file at the below link to reflect my sample data.
https://drive.google.com/file/d/1Ww39-2Ls18TwdFKBxKsrwZuocIyf-17V/view?usp=sharing
Expected output:
Mosby, Ted recieved Corrective Action with a start date of 11/3/2020.
He has a total of 8 leaves on the following dates:
Leave of Absence: 1/1/20, 1/2,20, 1/3/20, 1/6/20, and 1/7/20
Absence: 1/1/21, 1/2/21, 1/3/21
As you can see, only 3 of these dates fall after the start of his corrective action. So I would like it to filter out 1/1/20-1/7/20. So the ABS/LoA Days column (Or alternatively your measure) should show a count of 3 for Ted and the visual on the right-hand side will only show me those dates that fall after the Date Started (though really it's the count that's important, I can live with the visual on the right still showing me the additional days).
I hope this makes sense... please let me know if you need further clarification.
Hi Anonymous
I modify the reopen date to 2019/11/25 and 2019/12/05.
Here is my output:
Maybe there are some formatting errors in your excel table.
Here is the demo, please try it: demo.pbix
Best Regards,
Link
For your date format, are you using MM/DD/YYYY or DD/MM/YYYY?
I can see your measure is counting on the demo, but it's still not filtering out the dates after Ted recieved his corrective action on 11/3/2020 (MM/DD/YYYY). It is still showing dates from the months prior to November.
Okay, I got it working! Is there any way I can have it look for multiple pay codes or should I just do a different measure for each? For example, currenlyt it looks for Pay Cod=Leave of Absence and counts those, is there a way to, for instance, count both the Leave of Absence pay code and others, such as Military Leave?
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
That's exactly what I was looking for! Thank you so much for all your help!
