How to get sales and compare them with a custom calendar?

[EgMorel]

Hello everyone,

I want to find a way to calculate the sales from this month and compares it against the same month of an specific year, for example,

I have the sales between March 1st and March 13th 2023 and I want to compare it against 2019 same period but the difficulty is that the "Same Period in 2019" is not the "Same Period" what I mean is that it should be compared with a custom calendar, and to get the Customised 2019 calendar is subtract 2023 day with 1463 (i.e,. the comparableday of 2023/03/14 is 2019/03/12)

So, im going to explain my model with my tables in a simply way:

Actually, Im working with:

1. DETALLE_GRUPO (Sales Table with information from 2018 until today)

        - "Importe" (Sales in $)

        - "Fecha" (Date when the sales was made (normal calendar))

        - "Local" (Store where the sales was made)

2. Calendar2019 (Dates table)

       - "Custom2019" (the custom 2019 calendar substracting 2023 - 1463).

       - "2023" (normal calendar), so each row of 2023 should match with Custom2019.

I made a relationship with Calendar2019[2023] and DETALLE_GRUPO[Fecha].

I hope you can help me and sorry for my English.

Regards.

Eduardo

[tamerj1]

Hi @EgMorel 

Why not just keep in one table and create two relationships the use USERELATIONSHIP to switch between them?

[EgMorel]

Hi @tamerj1, 

Im not an expert in Power BI, so I came here asking for help. I tried different ways an no one works for me.
I tried working with just one Calendar Table with two columns but it doesnt works (I think a problem with relationships).

This is I want to achieve:

Date	Sales Store 1 (Normal Calendar)	Sales Store 1 (Custom Calendar)
2023-03-01	$1000	$500 (this should be get from 2019-02-27)
2023-03-02	$800	$600 (this should be get from 2019-02-28)

[tamerj1 · Accepted solution]

@EgMorel 

I think if you keep one date table and you create a 2nd (inactive) relationship between Calendar2019[Custom2019] and DETALLE_GRUPO[Fecha], then for 2019 the sales amount should calculate correctly using 

Sales 2019 = 

CALCULATE (

[Sales Amount],
USERELATIONSHIP ( Calendar2019[Custom2019], DETALLE_GRUPO[Fecha] )

)

Incase the result was all blank then try

Sales 2019 = 

CALCULATE (

[Sales Amount],
USERELATIONSHIP ( Calendar2019[Custom2019], DETALLE_GRUPO[Fecha] ),

CROSSFILTER ( Calendar2019[2023], DETALLE_GRUPO[Fecha], NONE )

)

[EgMorel]

I will try this option but I didnt get the 1st relationship? because we are going to work with just one date table "Calendar2019" and "DETALLE_GRUPO", you mean have two relationship between the same tables?

[tamerj1]

@EgMorel 

Yes exactly. One would be active [2023] and one by default would be inactive [Custom2019]

[EgMorel]

@tamerj1,
I get the same result as the @johnt75's way, 2019Sales is your measure (with the first formula) and Sales2019 is the @johnt75 way

[tamerj1]

@EgMorel 

Place the months in the table to see it clearly. I would expect to see correct values for the first 3 months, however, after that you would still be able to see values under 2019 sales while 2023 sales should be blank being future months. If you to see values only upto date you can try

Sales 2019 = 

CALCULATE (

[Sales Amount],
USERELATIONSHIP ( Calendar2019[Custom2019], DETALLE_GRUPO[Fecha] ),

KEEPFILTERS ( Calendar2019[Custom2019] <= TODAY ( ) )

)

[EgMorel]

@tamerj1 this is the Result with the months as rows:

[tamerj1]

@EgMorel 

Sorry just noticed a mistake in the last formula. Please try 

Sales 2019 = 

CALCULATE (

[Sales Amount],
USERELATIONSHIP ( Calendar2019[Custom2019], DETALLE_GRUPO[Fecha] ),

KEEPFILTERS ( Calendar2019[2023] <= TODAY ( ) )

)

[EgMorel]

@tamerj1 the formula doesnt work, there is a third expression in CALCULATE?

[tamerj1]

@EgMorel 
What do you mean? Are you recieving an error? Yu may also try

Sales 2019 =
CALCULATE (
    [Sales Amount],
    USERELATIONSHIP ( Calendar2019[Custom2019], DETALLE_GRUPO[Fecha] ),
    FILTER ( Calendar2019, Calendar2019[2023] <= TODAY () )
)

Please provide a screenshot of the DAX and results hiding any sensetive data 

[EgMorel]

Sorry, my mistake, I forgot the comma after USERELATIONSHIP  hahaha 
Aparently, its working fine.Now, to be sure that the 2019Sales are working fine, How can I know that for example the date 2023-03-14 is compared against 2019-03-12? Can I obtain those values?

2019Sales = 
CALCULATE( DETALLE_GRUPO[TotalSales_2023], USERELATIONSHIP('Tabla Calendario'[Custom2019], DETALLE_GRUPO[Fecha]), KEEPFILTERS ('Tabla Calendario'[Date] <= TODAY ()) ) 

Thanks for your support, I gave myself too many turns and the solution was not that difficult 😔

[tamerj1]

@EgMorel 
No problem 🙂 
you can create hard codded measures like 

CALCULATE( [TotalSales_2023], 'Tabla Calendario'[Date] = DATE ( 2019, 3, 12 ) ) 

and place it in a card visual

[EgMorel]

@tamerj1, check this pls, the information opened by day and in card visual the last formula that points to 2019-03-12, I cant see that value in the 2019Sales, is that correct?

[tamerj1]

@EgMorel 
Ok, I think it is the time to ask you what is the difference between Calendar2019[Date] and Calendar2019[2023]? 😂

[EgMorel]

@tamerj1
Ok 😁, but in my Calendar table I only have "Date" as a normal calendar and Custom2019 as my Custom Calendar 😋. So, I realized that 2019Sales is OK because the sales in 2023-03-13 for 2019Calendar is $28,564,576 and if I look through the sales in that period (which is 2019-03-11) I get that value:

[tamerj1]

@EgMorel 

As expected. But which one is the latest formula with TODAY filter?

[EgMorel]

@tamerj1, Im using this one:

2019Sales = 
CALCULATE(
        DETALLE_GRUPO[TotalSales_2023],
        USERELATIONSHIP('Tabla Calendario'[Custom2019], DETALLE_GRUPO[Fecha]),
        KEEPFILTERS ('Tabla Calendario'[Date] <= TODAY ())
)

and this is the result:

[tamerj1]

@EgMorel 
Is it good or bad?

To exclude today you use

2019Sales = 
CALCULATE(
        DETALLE_GRUPO[TotalSales_2023],
        USERELATIONSHIP('Tabla Calendario'[Custom2019], DETALLE_GRUPO[Fecha]),
        'Tabla Calendario'[Date] < TODAY ()
)

[EgMorel]

is not good or bad, its PERFECT!!!