topic Re: DAX measure for counting sales in the 90 days after an appointment date in DAX Commands and Tips

DAX measure for counting sales in the 90 days after an appointment date

gingerclaire — Tue, 01 Nov 2022 14:54:28 GMT

I have a PBI report comprising of (amongst other things):

Sales table
Date Table
Appointment dates and practice names

I want to find a measure that will allow me to create a table to show all the appointments in the last year and a measure to calculate how many sales (£) happened in the 90 days AFTER that specific appointment date.

Practice name	Date of appointment	Sales in 90 days after appointment date
XXX	1 june 2022	£45
YYY	7th October 2022	£0

Re: DAX measure for counting sales in the 90 days after an appointment date

lukiz84 — Tue, 01 Nov 2022 15:46:01 GMT

Sales = VAR DateOfApp = SELECTEDVALUE('AppointmentDates'[Date of appointment]) RETURN CALCULATE( SUM(Sales[Amount]), Sales[Date] >= DateOfApp && Sales[Date] <= DateOfApp + 90 )

Re: DAX measure for counting sales in the 90 days after an appointment date

gingerclaire — Tue, 01 Nov 2022 15:57:15 GMT

Thank you - that does indeed return a result, but that seems to only give me appointments from before 90 days ago? i e the most recent appointment it shows is in July this year - when I know they run right up to today?

Re: DAX measure for counting sales in the 90 days after an appointment date

lukiz84 — Tue, 01 Nov 2022 16:05:23 GMT

In your sample only the july one has sales afterwards... do the other appointments have sales? if not, the measure returns blank and then it's not shown on the matrix. If you use this measure, you should see all the appointsments, even if there were no sales:

Sales = VAR DateOfApp = SELECTEDVALUE('AppointmentDates'[Date of appointment]) RETURN CALCULATE( SUM(Sales[Amount]), Sales[Date] >= DateOfApp && Sales[Date] <= DateOfApp + 90 ) + 0

Re: DAX measure for counting sales in the 90 days after an appointment date

gingerclaire — Tue, 01 Nov 2022 16:14:17 GMT

Some will have sales and some will not.

But it is only showing appointments that were more than 90 days ago (i.e. not showing recent appointments, some of which will also have sales). I guess because the 90- day period is not finished yet. I think it needs to count up to present day but I am not sure how to do that? i./e.

Sales = VAR DateOfApp = SELECTEDVALUE('AppointmentDates'[Date of appointment]) RETURN CALCULATE( SUM(Sales[Amount]), Sales[Date] >= DateOfApp && Sales[Date] <= DateOfApp + 90 (or present day) ) + 0

Re: DAX measure for counting sales in the 90 days after an appointment date

lukiz84 — Tue, 01 Nov 2022 16:15:29 GMT

"will have sales" -> do they have them in your data NOW? if not, nothing is returned. Just add the + 0 to the Calculate function and you will see _all_ appointments 🙂

Re: DAX measure for counting sales in the 90 days after an appointment date

gingerclaire — Tue, 01 Nov 2022 16:17:44 GMT

Some of them DO currently have sales 🙂 (not will have - poor use of english by me there)

I have added the + 0 and still the appointments only show up to 29/07/22.

Re: DAX measure for counting sales in the 90 days after an appointment date

lukiz84 — Tue, 01 Nov 2022 17:21:16 GMT

ok, shouldn't be the problem with present date because of the <=

Do you maybe have some sample data which I could copy to test it on my machine?

Re: DAX measure for counting sales in the 90 days after an appointment date

lukiz84 — Wed, 02 Nov 2022 12:44:34 GMT

It Helps a little, do you have the appointment date on the matrix or the date from the date table? 🙂

Re: DAX measure for counting sales in the 90 days after an appointment date

gingerclaire — Wed, 02 Nov 2022 12:53:52 GMT

The appointment date is in the appointment table.

The date in the Sales table is only obtained on 1st of each month so is 1st July, 1st August only.

Re: DAX measure for counting sales in the 90 days after an appointment date

lukiz84 — Wed, 02 Nov 2022 12:58:11 GMT

No, i mean which field do you have on the matrix where you add the measure... Date from the Date table or appointment date from the appointments table?

Re: DAX measure for counting sales in the 90 days after an appointment date

gingerclaire — Wed, 02 Nov 2022 13:04:50 GMT

I think it is working 🙂

thanks you so much!

Re: DAX measure for counting sales in the 90 days after an appointment date

lukiz84 — Wed, 02 Nov 2022 13:07:18 GMT

how? 🙂

Re: DAX measure for counting sales in the 90 days after an appointment date

gingerclaire — Wed, 02 Nov 2022 13:07:47 GMT

Ah i see! it's a table 🙂

I have the following columns:
Practice Name Appointment date (from appointment table) and the measure.

Re: DAX measure for counting sales in the 90 days after an appointment date

gingerclaire — Fri, 28 Feb 2025 15:13:22 GMT

Hi - I am back working on this again. I have it working but is there a way to work out the sales made from the 1st of the month the appointment was in i.e.

Appointment date is 23rd february but i want to look for sales from 1st february and then 40 days on from there?

Our sales data all comes in dated 1st of each month, so I need to be able to include sales from the 1st of the month the appointment was in..