Dynamic Date Filter Help Please

[Daysy]

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

[v-yangliu-msft · Accepted solution]

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

[smpa01]

@Daysy  can you use a Calendar Table to do all the slicing, it would be way easier.

[Daysy]

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

[Anonymous]

Will the last movement date be in the visual? Then when adding the date to the visual, choose latest date. Then when you use a date filter, it will show you the max date of the date filtered.

[Daysy]

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.