sum amt when the filter date and the target date are different and group by staff

[miinseon]

My data has multiple dates, including booking date, payment date, and refund date. See the image below.

Sometimes the booking date and payment date are different.

For example, a customer comes in for a consultation today and pays tomorrow.

And i have a staff member in charge of it.

I want the slicer criteria to be the appointment date, and the sum of the payment amounts corresponding to the slicer to be by staff.
Only staff A is actually paying, but all staffs are exposed. Please advise how to fix this.

I'm attaching the pbix file as well.

pbix

[Irwan]

Hello @miinseon 

ah, i see the problem.

is this what you need?

if so, then you might want to change column relationship.

initially it was set up to 'RESERVE_DATE'

try changing it into 'PAYMENT_DATE'

As you can see below, staff A has payment date on 2July2024, but the relationship was connected to 'RESERVE_DATE' which is 1July2024. This makes the data doesnt show up because the relationship says it is 1July2024 from 'RESERVE_DATE', not 2July2024 from 'PAYMENT_DATE'.

is it possible to change the relationship? or does the relationship have to be in 'RESERVE_DATE'?

Hope this will help you.

Thank you.

[miinseon]

Hellow @Irwan 
Actually i have more KPIs related reserve date like reserve_cnt and show each row data based on reserve_data

so i think i will be better relationship in reserve_date

Thank you 🙂

[Irwan · Accepted solution]

Hello @miinseon 

basically you want to show a value that has been filtered out by slicer, I am not sure if you can do this.

your slicer is connected to 'RESERVE_DATE'. There is no value for staff A on 2July2024 in 'RESERVE_DATE' (staff A has value on 1July2024 in 'RESERVE_DATE').

So, here are the options: 
1. Change relationship to 'PAYMENT_DATE'.

2. If you dont want to re-arrange current table/relationship but insist to show the visual as you want, then i can offer what i did in my project.

- make another dummy table to visualize this conflict. Because this is dummy table, then you can create another relationship to 'PAYMENT_DATE' and will not change the current relationship in Sheet1 ('RESERVE_DATE').

- create exact measure and visualize the data on the dummy table (not the Sheet1 table since the relationship on Sheet1 is still on 'RESERVE_DATE').

If this method doesnt suit your, maybe other Power BI experts have more effective solution.

Thank you.

[miinseon]

HI @Irwan 
It's not 100% right for me, but it seems like a good way to go.

I'll give it a try.

Thank you so much

[Irwan]

Hello @miinseon 

From what i have seen in your DAX, looks like you are using ALL that makes all staff got same value.

and if ALL function was removed and change ALL into table, it will work (only show related staff).

 Hope this will help you.

Thank you.

[miinseon]

The method you told me worked.
However, when I changed the slicer date to July 2, I should get data for 2 agents.
The relationship is set to the reserve date, but the actual graph should only take into account the payment date.
That's why I tried using all in the first place.

Can you give me a solution for this?