Question - comparative date

[Stephany]

05/07/2021	01/07/2019
06/07/2021	02/07/2019
07/07/2021	03/07/2019
08/07/2021	04/07/2019
09/07/2021	05/07/2019
10/07/2021	06/07/2019
11/07/2021	07/07/2019
12/07/2021	08/07/2019
13/07/2021	09/07/2019
14/07/2021	10/07/2019
15/07/2021	11/07/2019
16/07/2021	12/07/2019
17/07/2021	13/07/2019
18/07/2021	14/07/2019
19/07/2021	15/07/2019
20/07/2021	16/07/2019
21/07/2021	17/07/2019
22/07/2021	18/07/2019
23/07/2021	19/07/2019
24/07/2021	20/07/2019
25/07/2021	21/07/2019
26/07/2021	22/07/2019
27/07/2021	23/07/2019
28/07/2021	24/07/2019
29/07/2021	25/07/2019
30/07/2021	26/07/2019
31/07/2021	27/07/2019
01/07/2021	28/07/2019
02/07/2021	29/07/2019
03/07/2021	30/07/2019
04/07/2021	31/07/2019

Hello, 

I have this data and i need to show 2 things

The total amount from the start of this month until today
(sum({<Fecha={">=$(=date(min(Fecha)))<=$(=date(Max(Fecha)))"}>} VentaNeta84Sus)

and the same for 2019.. as the comparative table before

so i can not use a range of date, as the date does not have an specific order.

I tried something like this:

(sum({<Fecha={"=$(=date(FechaB))"}>} VentaNeta84Sus)

But it does not work

Any advice?

[JuanGerardo]

Hi @Stephany  I think the best way is to use a table between your calendar table and your fact table, with the dates but duplicated for every direct date and comparative combination, using a flag. It could be something like this:

Fact Table:

FactDate, Sales

05/07/2021, 100€

06/07/2021, 23€

Calendar Table:

Date, Month, Year

05/07/2021, Jul, 2021

06/07/2021, Jul, 2021

Comparative Table:

Date, FactDate, IsComparative

05/07/2021, 05/07/2021, 0

05/07/2021, 01/07/2019, 1

06/07/2021, 06/07/2021, 0

06/07/2021, 02/07/2019, 1

Then you can include the condition IsComparative=0 in all your current expressions, or activate "always one seleted value" on IsComparative. Thus, your expressions could be:

For 2021 sales: Sum({<IsComparative={0}, Date={">=$(=date(min(Date)))<=$(=date(Max(Date)))"}>} Sales)

For 2019 sales: Sum({<IsComparative={1}, Date={">=$(=date(min(Date)))<=$(=date(Max(Date)))"}>} Sales)

JG