multiple conditions + calculation

[Olboss_384]

Hi Community,

following problem.

My calculation with multiple conditions including addition does not work

Here is my formula that doesn't work:

 If(and( Category ="Küche" && Order >(Date(2021, 12, 31) && Order < (Date(2022, 12, 31), Calculate(0*200) +

 If(and( Category ="Küche" && Shiptment>(Date(2021, 12, 31) && Order < (Date(2022, 12, 31), Calculate(0,25*200) + 

If(and( Category ="Küche" && IBN>(Date(2021, 12, 31) && Order < (Date(2022, 12, 31), Calculate(0,5*200) +

If(and( Category ="Küche" && Ready>(Date(2021, 12, 31) && Order < (Date(2022, 12, 31), Calculate(0,25*200) )))

Does anyone have an idea how I can do this calculation in Power BI?

[Olboss_384]

https://iubhfs-my.sharepoint.com/:u:/r/personal/oliver_eitner_iu-study_org/Documents/PBI/Test%20AMZ%...

[Samarth_18]

@Olboss_384  I think you have uploaded it on your office drive and we dont have access to it. Better try to give access to all or use wetranfer.com or similar to it.

[Samarth_18]

Hi @Olboss_384 ,

Create a column with below code

Column =
SWITCH (
    TRUE (),
    Category = "Küche"
        && Order > DATE ( 2021, 12, 31 )
        && Order < DATE ( 2022, 12, 31 ), 0 * 200,
    Category = "Küche"
        && Shiptment > DATE ( 2021, 12, 31 )
        && Order < DATE ( 2022, 12, 31 ), 0.25 * 200,
    Category = "Küche"
        && Shiptment > DATE ( 2021, 12, 31 )
        && Order < DATE ( 2022, 12, 31 ), 0.25 * 200,
    Category = "Küche"
        && IBN > DATE ( 2021, 12, 31 )
        && Order < DATE ( 2022, 12, 31 ), 0.5 * 200,
    Category = "Küche"
        && Ready > DATE ( 2021, 12, 31 )
        && Order < DATE ( 2022, 12, 31 ), 0.25 * 200
)

and then create a measure like below or directly used new column as summerized as sum

Measure = Sum(table[column])

Thanks,

Samarth

[Syndicate_Admin]

Thanks for your help - Unfortunately it doesn't work.

[Samarth_18]

@Syndicate_Admin , Remove last comma(",") from the last line i.e. line 28.

[Olboss_384]

[Samarth_18]

@Olboss_384 is it possible for you to share PBIX file after removing sensitive data?

[Syndicate_Admin]

Yes, it is possible, so I have created a sample PBI file. How can I send them to you?

[Samarth_18]

@Syndicate_Admin  upload it in any suitable drive and share the URL. 

[Syndicate_Admin]

https://iubhfs-my.sharepoint.com/:u:/r/personal/oliver_eitner_iu-study_org/Documents/PBI/Test%20AMZ%...

[Syndicate_Admin]

https://iubhfs-my.sharepoint.com/:u:/r/personal/oliver_eitner_iu-study_org/Documents/PBI/Test%20AMZ%...

[Samarth_18]

@Syndicate_Admin Not accessible.

[Olboss_384]

Sorry.

https://www.transfernow.net/dl/20220121hODFm7ZL

[Samarth_18]

@Olboss_384 , Please try this:-

Column = 
if(Tabelle2[category] = "küche"&& Tabelle2[Order]  > DATE ( 2021, 12, 31 )&& Tabelle2[Order] < DATE ( 2022, 12, 31 ), 0 * 200,

if(Tabelle2[category] = "küche"&& Tabelle2[Shipment]  > DATE ( 2021, 12, 31 )&& Tabelle2[Shipment] < DATE ( 2022, 12, 31 ), 0.25 * 200,

IF( [category] = "küche" && [IBN]  > DATE ( 2021, 12, 31 ) && [IBN] < DATE ( 2022, 12, 31 ), 0.5 * 200,

if(Tabelle2[category] = "küche"&& Tabelle2[Ready]  > DATE ( 2021, 12, 31 )&& Tabelle2[Ready] < DATE ( 2022, 12, 31 ), 0.5 * 200,

if(Tabelle2[category] = "Bad"&& Tabelle2[Order]  > DATE ( 2021, 12, 31 )&& Tabelle2[Order] < DATE ( 2022, 12, 31 ), 0 * 200,

if(Tabelle2[category] = "Bad"&& Tabelle2[Shipment]  > DATE ( 2021, 12, 31 )&& Tabelle2[Shipment] < DATE ( 2022, 12, 31 ), 0.25 * 200,

if(Tabelle2[category] = "Bad"&& Tabelle2[IBN]  > DATE ( 2021, 12, 31 )&& Tabelle2[IBN] < DATE ( 2022, 12, 31 ), 0.5 * 200,

if(Tabelle2[category] = "Bad"&& Tabelle2[Ready]  > DATE ( 2021, 12, 31 )&& Tabelle2[Ready] < DATE ( 2022, 12, 31 ), 0.5 * 200

))))))))

Departure:-

Thank you

Samarth

[Syndicate_Admin]

Thank you for the help, it works.

Is it possible to define the date in the code as a variable so that I do not have to write this out?

--> In case I need to change the date, I would only have to do this in the variable and not in every line.

For example

[Samarth_18]

@Syndicate_Admin Okay try this then:-

Column = 
var _yr_2021 = DATE ( 2021, 12, 31 )
var _yr_2022 = DATE ( 2022, 12, 31 )
return if(Tabelle2[category] = "küche"&& Tabelle2[Order]  > _yr_2021&& Tabelle2[Order] < _yr_2022, 0 * 200,

if(Tabelle2[category] = "küche"&& Tabelle2[Shipment]  > _yr_2021&& Tabelle2[Shipment] < _yr_2022, 0.25 * 200,

IF( [category] = "küche" && [IBN]  > _yr_2021 && [IBN] < _yr_2022, 0.5 * 200,

if(Tabelle2[category] = "küche"&& Tabelle2[Ready]  > _yr_2021&& Tabelle2[Ready] < _yr_2022, 0.5 * 200,

if(Tabelle2[category] = "Bad"&& Tabelle2[Order]  > _yr_2021&& Tabelle2[Order] < _yr_2022, 0 * 200,

if(Tabelle2[category] = "Bad"&& Tabelle2[Shipment]  > _yr_2021&& Tabelle2[Shipment] < _yr_2022, 0.25 * 200,

if(Tabelle2[category] = "Bad"&& Tabelle2[IBN]  > _yr_2021&& Tabelle2[IBN] < _yr_2022, 0.5 * 200,

if(Tabelle2[category] = "Bad"&& Tabelle2[Ready]  > _yr_2021&& Tabelle2[Ready] < _yr_2022, 0.5 * 200

[Syndicate_Admin]

Hi, I just noticed that the values are not added up.

Can you please help me again.

[Samarth_18]

@Syndicate_Admin , It is not required. You can directly use this column on your visual and mark summerization as SUM  or create a measure with below code and use it:-

measure = Sum(Tabelle2[column])

[Olboss_384]

Thank you. But I do not want to make the sum of all projects, such as summerization.

Calculation within the formula would need to be adjusted.
In the formula the result is 100, but it should be 200.