if statment based on 3 tables link with column date

[URobert]

Hi,

I have the below situation and i would need your help.

I have 3 tables and i want to create an if statment to calculate based on date column and customer.

Table 1:

Table 2

Table 3

I want to calculate for each customer based on date :

Point 1. -> formula A = table 1 - table 2 (on date and customer level)

Point 2:  Formula B = if(formula A < 0 ; formula A * (price 1 - price 3) ; formula A * (price 1 - price 2))

Thank you.

[amitchandak]

@URobert , this not the best data structure. First, unpivot tables and then create common date and customer dimensions. Then you should able to perform these calculations

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[URobert]

hi,

I've unpivot the columns tables, but my issue is how i could calculate an If statment based on tables link only by dates.

Table 1

Data	Customer name	Value
01.01.2020	Customer A	0,80
01.01.2020	Customer B	0,10
01.01.2020	Customer C	0,61
02.01.2020	Customer A	0,51
02.01.2020	Customer B	0,82
02.01.2020	Customer C	1,17
03.01.2020	Customer A	0,59
03.01.2020	Customer B	0,66
03.01.2020	Customer C	1,08
04.01.2020	Customer A	0,72
04.01.2020	Customer B	0,37
04.01.2020	Customer C	0,72

Table 2

Data	Customer name	Value
01.01.2020	Customer A	0,00
01.01.2020	Customer B	0,80
01.01.2020	Customer C	0,10
02.01.2020	Customer A	0,72
02.01.2020	Customer B	0,51
02.01.2020	Customer C	0,82
03.01.2020	Customer A	0,56
03.01.2020	Customer B	0,59
03.01.2020	Customer C	0,66
04.01.2020	Customer A	0,27
04.01.2020	Customer B	0,72
04.01.2020	Customer C	0,37

table 3

Data	price	Value
01.01.2020	price 1	200
01.01.2020	price 2	100
01.01.2020	price 3	300
02.01.2020	price 1	200
02.01.2020	price 2	100
02.01.2020	price 3	300
03.01.2020	price 1	200
03.01.2020	price 2	100
03.01.2020	price 3	300
04.01.2020	price 1	200
04.01.2020	price 2	100
04.01.2020	price 3	300

Table 3 has only dates.

I nees to link all this 3 tables and to create the below formulas :

       Formula A = table 1 - table 2 (on date and customer level)

       Formula B = if(formula A < 0 ; formula A * (price 1 - price 3) ; formula A * (price 1 - price 2))

And to be able to create this visualization :

data	Customer	table 1 value	table 2 value	price 1	price 2	price 3	Formula A	Formula B
01.01.2020	Customer A	0,80	0,00	200	100	300	0,80	79,60
01.01.2020	Customer B	0,10	0,80	200	100	300	-0,70	69,60
01.01.2020	Customer C	0,61	0,10	200	100	300	0,51	50,91
02.01.2020	Customer A	0,51	0,72	200	100	300	-0,21	20,94
02.01.2020	Customer B	0,82	0,51	200	100	300	0,31	30,94
02.01.2020	Customer C	1,17	0,82	200	100	300	0,34	34,38
03.01.2020	Customer A	0,59	0,56	200	100	300	0,03	2,92
03.01.2020	Customer B	0,66	0,59	200	100	300	0,07	7,08
03.01.2020	Customer C	1,08	0,66	200	100	300	0,42	42,24
04.01.2020	Customer A	0,72	0,27	200	100	300	0,45	45,02
04.01.2020	Customer B	0,37	0,72	200	100	300	-0,35	35,02
04.01.2020	Customer C	0,72	0,37	200	100	300	0,36	35,62

[Anonymous · Accepted solution]

HI @URobert,

You can extract date and customer value from raw table to create new calculate tables, then you can use them as the category to write measure formulas to lookup corresponded values based on its category fields.

Calculate tables:

Calendar = 
DISTINCT ( UNION ( ALL ( C1[Data] ), ALL ( C2[Data] ), ALL ( P1[Data] ) ) )

Customer =
DISTINCT ( UNION ( ALL ( C1[Customer name] ), ALL ( C2[Customer name] ) ) )

Measures:

C1Value = 
CALCULATE (
    SUM ( C1[Value] ),
    FILTER (
        ALLSELECTED ( C1 ),
        [Data] IN VALUES ( 'Calendar'[Data] )
            && [Customer name] IN VALUES ( Customer[Customer name] )
    )
)

C2Value = 
CALCULATE (
    SUM ( C2[Value] ),
    FILTER (
        ALLSELECTED ( C2 ),
        [Data] IN VALUES ( 'Calendar'[Data] )
            && [Customer name] IN VALUES ( Customer[Customer name] )
    )
)

P1 = 
CALCULATE (
    SUM ( P1[Value] ),
    FILTER (
        ALLSELECTED ( P1 ),
        [Data] IN VALUES ( 'Calendar'[Data] )
            && [price] = "price 1"
    )
)

P2 = 
CALCULATE (
    SUM ( P1[Value] ),
    FILTER (
        ALLSELECTED ( P1 ),
        [Data] IN VALUES ( 'Calendar'[Data] )
            && [price] = "price 2"
    )
)

P3 = 
CALCULATE (
    SUM ( P1[Value] ),
    FILTER (
        ALLSELECTED ( P1 ),
        [Data] IN VALUES ( 'Calendar'[Data] )
            && [price] = "price 3"
    )
)

Diff = 
[C1Value] - [C2Value]

Result = 
IF ( [Diff] < 0, [Diff] * ( [P1] - [P3] ), [Diff] * ( [P1] - [P2] ) )

Regards,

Xiaoxin Sheng