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

[gingerclaire]

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

[lukiz84]

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

[gingerclaire]

I think it is working 🙂

thanks you so much!

[lukiz84]

how? 🙂

[gingerclaire]

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.

[lukiz84]

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?

[gingerclaire]

Ah i see! it's a table 🙂

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

[lukiz84]

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

[gingerclaire]

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

[lukiz84]

"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 🙂

[gingerclaire]

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.

[lukiz84]

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?

[lukiz84 · Accepted solution]

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

[gingerclaire]

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..

[gingerclaire]

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?