Forum Discussion
Dynamic Date Filter Help Please
Hi All,
Have been using PBI for a year or so and this site has been a great resource, but in this instance my Google-Fu is failing me.
I have an extensive table that shows each payment a customer makes with a movement number and date. The movement shows the number of updates on the account (first time it hits the table it's movement 1, next update is movement 2 etc) and the date that movement occured. What I would like to be able to do is add a filter to my report for the last day of each month and for my table to filter down to the last movement for each customer that was before that month end date.
For example, I would like to filter on 31/05/2020 and my table to filter to Customer A, Movement 2 and Customer B, Movement 1. Or Filter to 30/04/2021 and my table would filter to Customer A, Movement 3,Customer B, Movement 3.
| Customer | Last Movement Date | Paid | Outstanding | Movement |
| Customer A | 02/01/2019 | 1000 | 3000 | 1 |
| Customer A | 01/05/2020 | 1000 | 2000 | 2 |
| Customer A | 28/08/2020 | 1000 | 1000 | 3 |
| Customer B | 01/05/2020 | 5000 | 6000 | 1 |
| Customer B | 01/01/2021 | 3000 | 3000 | 2 |
| Customer B | 08/04/2021 | 1500 | 1500 | 3 |
I've got as far as grouping the customer name and can pull the latest movement from that, but I can't work out a way to combine it with a filter to pick up only the last movement number before the month end date.
Any help gratefully received!
Thanks
Daysy
Hi Daysy ,
Here are the steps you can follow：
1. Create calendar.
Date_Table = CALENDARAUTO()2. Create calculated column
date_end = EOMONTH('Date_Table'[Date],0)3. Create measure.
Flag = var _select=SELECTEDVALUE('Date_Table'[date_end]) return IF( MONTH(_select)=MONTH(MAX('Table'[Last Movement Date])),1,0) Place [Flag] in Filters, set is=1, apply filter.4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Thanks. It's a table visual I'm using as I want the end user to be able to extract the filtered table to Excel. I guess another way to look at it is that I want to be able to add a date filter and only pick up the last movement before that date, not all of the movements before that date.
Yes, I'm reasonably comfortable using calendar tables and could add one in to this. It's the only way I could think to pick up the last days of the month and filter that way, but I'm not sure how to implement that.
Anonymous solution has got me part way there, thank you, using the latest movement date and latest movement number in the table and using the movement date in the slicer, but I would like to take it a step further and just have month end dates to slice by.
Thanks
Daysy
Hi Daysy ,
Here are the steps you can follow：
1. Create calendar.
Date_Table = CALENDARAUTO()2. Create calculated column
date_end = EOMONTH('Date_Table'[Date],0)3. Create measure.
Flag = var _select=SELECTEDVALUE('Date_Table'[date_end]) return IF( MONTH(_select)=MONTH(MAX('Table'[Last Movement Date])),1,0) Place [Flag] in Filters, set is=1, apply filter.4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
