Calculation of CA event in 2024 and equivalent event date in 2023

[CVERHAEGHE]

Hello,

I have two tables :

1. Database of all Events with

- the name of the event + the day number (day one, day two, etc. of the event)
- the date of the event in 2024

- the date of the event in 2023

2. Database with all my Sales informations (ex : for line 01/01/2024, REV EUR CY = Revenue of the 01/01/2024, REV EUR LYTD = Revenue of the 01/01/2023). I added a column EVENTS 2024 and EVENTS 2023 thanks to a lookupvalue.

I want to have in my Sales table a measure or a colum with the Revenue of the 2024 events (with REV EUR CY) and an other one with the Revenue of the 2023 events (with REV EUR LYTD).

I didn't link the two tables. I used as a global filter the column EVENT + NB DAY of Events table.

I used REV EUR EVENT 2024 = 

VAR events_2024 =ALLSELECTED('EVENTS'[EVENT + NB DAY])

return

calculate([REV EUR N],'Sales'[EVENTS 2024] in events_2024)

and

REV EUR EVENT 2023 = 

VAR events_2023 =ALLSELECTED('EVENTS'[EVENT + NB DAY])

return

calculate([REV EUR SPIT N-1],'Sales'[EVENTS 2023] in events_2023)

Here is the result : 

The problem is that I need to have the detail by EVENT + NB DAY.

I also have to be able to have details by SITE_CODE or Segment : I have it now but without the detail by EVENT + NB DAY.

Can you help me to have the details by EVENT + NB DAY ?

Thank you in advance,

[v-jianpeng-msft]

Hi, @CVERHAEGHE 

Based on your description, I created the following sample data:

sheet1

sheet2

I tested your DAX expression:

I also get the sum and each column is the same number. This is not correct.  I got the correct results by putting the period column aggregation in the condition of the IF function:

In this way, I correctly obtained the details corresponding to EVENT + NB DAY. I rewrote your DAX function below:

REV EUR EVENT 2024 =
VAR events_2024 =
    ALLSELECTED ( 'EVENTS'[EVENT + NB DAY] )
RETURN
    IF (
        SUM ( 'Sales'[EVENTS 2024] ) IN events_2024,
        CALCULATE ( [REV EUR N] ),
        BLANK ()
    )


REV EUR EVENT 2023 =
VAR events_2023 =
    ALLSELECTED ( 'EVENTS'[EVENT + NB DAY] )
RETURN
    IF (
        SUM ( 'Sales'[EVENTS 2023] ) IN events_2023,
        CALCULATE ( [REV EUR SPIT N-1] ),
        BLANK ()
    )

I hope the above suggestions will be helpful to you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[CVERHAEGHE]

Hi @v-jianpeng-msft,

Thank you for your help.

It doesn't seem to work. I have an error message because in the SUM, I have String values.

REV EUR EVENT 2024 =
VAR events_2024 =
    ALLSELECTED ( 'EVENTS'[EVENT + NB DAY] )
RETURN
    IF (
        SUM ( 'Sales'[EVENTS 2024] ) IN events_2024,
        CALCULATE ( [REV EUR N] ),
        BLANK ()
    )

 In your exemple, 'Sales'[EVENTS 2024] are dates but in my case, they are events name. Maybe that is the problem ?

Camille

[v-jianpeng-msft]

Hi, @CVERHAEGHE 

Can you provide a PBIX document that does not contain privacy?

[CVERHAEGHE]

@v-jianpeng-msft,

Here is an example of my database : PBI EVENT

Camille

[v-jianpeng-msft]

Hi, @CVERHAEGHE 

Thank you very much for your reply, but when I clicked on your link I found that the file had been deleted:

[CVERHAEGHE]

https://file.io/C8cA65FdxBNW

[v-jianpeng-msft · Accepted solution]

Hi, @CVERHAEGHE 

Thank you very much for the PBIX you provided. My idea is this: When I try 'EVENTS'[EVENT + NB DAY] to match 'Sales'[EVENTS 2024], a blank appears. So I checked 'Sales'[EVENTS 2024] and found that there was no matching item between 'Sales'[EVENTS 2024] and 'EVENTS'[EVENT + NB DAY]. The main reason was that 'EVENTS'[EVENT + NB DAY] was combined Ranking EVENT:

In 'Sales'[EVENTS 2024] there is no combined ranking of EVENTs:

This will cause calculation errors. I changed your expression for [EVENTS 2024] in the Sales table so that 'EVENTS' [EVENT + NB DAY] correctly matches the item in the Sales table.

EVENT 2024 =
VAR _date = 'SALES'[Date_2024]
RETURN
    LOOKUPVALUE (
        'EVENT'[EVENT],
        'EVENT'[KEY SITE + DATE 2024], SALES[KEY SITE + DATE 2024]
    )
        & CALCULATE (
            MAX ( 'EVENT'[NB DAY OF EVENT] ),
            'EVENT'[DAY OF EVENT 2024] = _date
        )
EVENT 2023 =
VAR _date = 'SALES'[Date_2023]
RETURN
    LOOKUPVALUE (
        'EVENT'[EVENT],
        'EVENT'[KEY SITE + DATE 2023], SALES[KEY SITE + DATE 2023]
    )
        & CALCULATE (
            MAX ( 'EVENT'[NB DAY OF EVENT] ),
            'EVENT'[DAY OF EVENT 2024] = _date
        )

In the above expression, I mainly extract the rankings in the EVENT table and merge them into 'Sales'[EVENTS 2024] and 'Sales'[EVENTS 2023]. The matching condition is 'EVENT'[DAY OF EVENT 2024] matches Date_2023 and Date_2024 in the Sales table.

I use the following DAX expression to find the details corresponding to EVENT + NB DAY OF EVENT:

REV EUR EVENT 2023 =
VAR events_2023 =
    VALUES ( 'EVENT'[EVENT + NB DAY OF EVENT] )
RETURN
    IF (
        COUNTROWS ( FILTER ( ALL ( 'Sales' ), 'SALES'[EVENT 2023] IN events_2023 ) ) > 0,
        CALCULATE (
            SUMX (
                FILTER ( 'SALES', 'SALES'[EVENT 2023] IN events_2023 ),
                'SALES'[REVENUE EUR 2023]
            )
        ),
        BLANK ()
    )


REV EUR EVENT 2024 =
VAR events_2024 =
    VALUES ( 'EVENT'[EVENT + NB DAY OF EVENT] )
RETURN
    IF (
        COUNTROWS ( FILTER ( ALL ( 'Sales' ), 'SALES'[EVENT 2024] IN events_2024 ) ) > 0,
        CALCULATE (
            SUMX (
                FILTER ( 'SALES', 'SALES'[EVENT 2024] IN events_2024 ),
                'SALES'[REVENUE EUR 2024]
            )
        ),
        BLANK ()
    )

The above DAX expression first extracts the value in 'EVENT'[EVENT + NB DAY OF EVENT], filters out 'SALES'[EVENT 2024]='EVENT'[EVENT + NB DAY OF EVENT] through FILTER, and then 'SALES'[REVENUE EUR 2024] performs the sum.

The result is as follows:

If you do not need to merge the rankings into 'Sales' [EVENTS 2024], you should modify the values in the var variable in the REV EUR EVENT 2024 measure as follows:

VAR events_2024 =
    VALUES('EVENT'[EVENT])

I've included the PBIX file below and if you have any questions I'll do my best to answer them for you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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