Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@Daysy can you use a Calendar Table to do all the slicing, it would be way easier.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |