Measure Net Sales calculated before and after a specific date from other table

[kta87]

Dear pro's,

I have to build two measures which are giving me a hard time. Both are almost identical, the only difference is the filtering, one is before and the second after or equal a specific date from other table. 

So the first table (SALES) that I've got is containing the net sales by customer ID, product ID, calendar day and net sales.

Customer ID	Product ID	Calendar Date	Net Sales
88	331458	07/12/2023	0.2
88	373205	16/11/2023	0.4
88	373205	09/11/2023	0.4
88	373205	23/11/2023	0.6
88	331947	07/12/2023	0.6
88	267946	16/11/2023	0.01
88	302647	09/11/2023	0.2
88	302647	07/12/2023	0.2
169	302647	09/11/2023	0.2
169	242152	02/11/2023	0.2
354	266150	23/11/2023	0.2
354	242152	07/12/2023	0.4
354	331947	07/12/2023	0.6
354	373205	16/11/2023	0.8
354	373205	23/11/2023	1.2

The second table (EVENTS) is containing the events details.

Customer ID	Event ID	Event Date
88	Event1	10/11/2023
88	Event2	16/11/2023
169	Event1	05/11/2023
354	Event1	12/11/2023
354	Event2	16/11/2023
354	Event3	23/11/2023

Both tables are linked to a Calendar Table (CALENDAR) and a Customer Table (CUSTOMER).

What I want to achieve is a table where I want to see the net sales before the event date and the net sales after or equal the event date.

Customer ID	Event ID	Event Date	Net Sales Before	Net Sales After
88	Event1	10/11/2023	0.8	2.01
88	Event2	16/11/2023	0.8	2.01
169	Event1	05/11/2023	0.2	0.4
354	Event1	12/11/2023	0	3.2
354	Event2	16/11/2023	0	3.2
354	Event3	23/11/2023	0.8	2.4

I've tried the following formula, but is not returning the correct values.

Before Event =

CALCULATE ([Net Sales],FILTER(SALES,SALES[Calendar Date]<MAX(EVENTS[Event Date])

Thank you in advance

[v-yifanw-msft]

Hello @kta87 ,

Thank you for your question. You can follow these steps:

1. Create a measure

 Net Sales Before = 
CALCULATE (
    SUM ( SALES[Net Sales] ),
    FILTER (
        ALL ( SALES ),
        'SALES'[Customer ID] = SELECTEDVALUE ( 'Table'[Customer ID] )
            && 'SALES'[Calendar Date] < SELECTEDVALUE ( 'Table'[Event Date] )
    )
)

2. Create another measure

    Net Sales After = 
CALCULATE (
    SUM ( SALES[Net Sales] ),
    FILTER (
        ALL ( SALES ),
        'SALES'[Customer ID] = SELECTEDVALUE ( 'Table'[Customer ID] )
            && 'SALES'[Calendar Date] >= SELECTEDVALUE ( 'Table'[Event Date] )
    )
)

How to Get Your Question Answered Quickly - Microsoft Fabric Community

 If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[kta87 · Accepted solution]

Hi @v-yifanw-msft ,

Thank you for the response. Unfortunately those measures are returning blank also.
Luckly I've managed to figure it out by my self. The correct measure was the first one posted by me in the initial request and all I had to do was to add a slicer (Single Select) for the EVENT ID.

[smpa01]

@kta87  can you try this

before = CALCULATE(SUM(Sales[Net Sales]), FILTER(All(Sales),Sales[Customer ID]=MAX(Event[Customer ID])&&Sales[Date]<=MAX(Event[Date])))

[kta87]

This measure is returning nothing, is blank.