topic Re: How to get sales and compare them with a custom calendar? in DAX Commands and Tips

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

EgMorel — Tue, 14 Mar 2023 12:49:26 GMT

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

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

johnt75 — Tue, 14 Mar 2023 15:48:16 GMT

I would split the calendar into 2, a normal calendar which includes all dates from Jan 1 2019 until Dec 31 2023 and a second one which has the date from 2023 and the appropriate date from 2019. Link them so that the normal calendar is the one side and the 2019 calendar is the many side and the filter flows from normal to 2019, not the other way. Link the normal calendar to your fact table and you can create a measure to compare them like

Compare 2023 v 2019 = VAR Value2023 = SUM ( 'DETALLE_GRUPO'[Importe] ) VAR Value2019 = CALCULATE ( SUM ( 'DETALLE_GRUPO'[Importe] ), TREATAS ( VALUES ( 'Calendar2019'[Date 2023] ), 'Calendar normal'[Date] ) ) RETURN DIVIDE ( Value2023 - Value2019, Value2019 )

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

EgMorel — Tue, 14 Mar 2023 16:24:07 GMT

Hi johnt75,

First of all, thanks for taking time to help me with my issue. To understand well, I should work with three tables?
1. Normal Calendar (2018-12-30 through 2023-12-31)
2. Custom Calendar (Column 2023 and column 2019 with the appropiate date)
3. DETALLE_GRUPO (Date and Importe)

The link between Normal Calendar and Custom Calendar should be column 2023 and the link with DETALLE_GRUPO should be with Normal Calendar? because a I have a column with the normal dates in that table.

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

johnt75 — Tue, 14 Mar 2023 16:28:47 GMT

Yes, exactly so. And the link from normal calendar to to custom calendar should only be single directional, from normal to custom.

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

EgMorel — Tue, 14 Mar 2023 17:32:59 GMT

John, I think Im close to achieving it but i don't know if the result is correct because my sales in this month (March until yesterday) are $70,629,048 and the measure Compare 2023 vs 2019 gives me 10,56%, I built a new measure called "Sales2019" to know what was the sale in that period in 2019 (with my custom calendar, it should be from 2019-02-27 until 2019-03-11) with a part of your DAX FORMULA:

CALCULATE (

SUM ( 'DETALLE_GRUPO'[Importe] ),

TREATAS ( VALUES ( 'Custom Calendar'[2023] ), 'NormalCalendar'[Date] ))

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

tamerj1 — Tue, 14 Mar 2023 18:05:28 GMT

Hi EgMorel

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

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

EgMorel — Tue, 14 Mar 2023 18:17:21 GMT

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)

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

tamerj1 — Tue, 14 Mar 2023 18:50:51 GMT

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 )

)

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

EgMorel — Tue, 14 Mar 2023 19:00:02 GMT

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?

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

tamerj1 — Tue, 14 Mar 2023 19:29:50 GMT

EgMorel

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

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

EgMorel — Tue, 14 Mar 2023 19:40:51 GMT

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

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

tamerj1 — Tue, 14 Mar 2023 20:02:03 GMT

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 ( ) )

)

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

EgMorel — Tue, 14 Mar 2023 20:08:09 GMT

tamerj1 this is the Result with the months as rows:

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

tamerj1 — Tue, 14 Mar 2023 20:23:06 GMT

EgMorel

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

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

tamerj1 — Tue, 14 Mar 2023 20:49:17 GMT

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 ( ) )

)

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

EgMorel — Tue, 14 Mar 2023 22:44:21 GMT

2019Sales were calculated with Your first fórmula.

Sales 2019 =

CALCULATE (

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

)

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

EgMorel — Wed, 15 Mar 2023 11:01:47 GMT

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

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

tamerj1 — Wed, 15 Mar 2023 11:13:24 GMT

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

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

EgMorel — Wed, 15 Mar 2023 11:24:07 GMT

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 😔

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

tamerj1 — Wed, 15 Mar 2023 11:55:25 GMT

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