IF statement and for each

[Anonymous]

Hello,

i am new using DAX, I am using a IF statement in a custom column to add some fixed values in my costs table, i am just adding the fixed values for some specific cases where i have other columns values that i applied to the IF.

Now i need to apply in this same custom column, another condition where i will need to use the date (month and year)

So using all IFs i already have and adding the values only when a line is before a monht/year or specific date.

i have a column Date and values is like this

11/11/2020 12:00:00 AM

This is my currently code for the custom column

if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "D2 v3/D2s v3"  then "0.07" 

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "D4 v3/D4s v3" then "0.15" 

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "D8 v3/D8s v3" then "0.30" 

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "D16 v3/D16s v3" then "0.59" 

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "E2 v3/E2s v3" then "0.09" 

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "E4 v3/E4s v3" then "0.19" 

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "E8 v3/E8s v3" then "0.38" 

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "E16 v3/E16s v3" then "0.76" 

else [Cost]

Can you help me to do this?

[Anonymous]

@gabrielreversi  could you help me?

[amitchandak]

@Anonymous , No very Clear. You can use Date.ToText to get year month and then use that in your formula

https://docs.microsoft.com/en-us/powerquery-m/date-totext

[Anonymous]

Hello, thanks for you answer.

Could you help me with more details,

your suggestion is to transform my date column into two new columns with the Month and Year information, (text), I could add this column as a condition to my previous formula?

There is a way to do it in a single line, instead of add the Month and Year condition for all lines in my previous formula?

l

[gabrielreversi]

Hello @Anonymous 

could you batter exemplify what you would like to do?

[Anonymous]

Hi, Gabriel, sure.

My current IF formula is adding a fixed price for lines where I have specific values for MeterCategory,  MeterRegion and MeterName and it is working fine. now I need to apply the same logic, only when the Month values is equal or before November 2020. My target is to add another custom column where I will do another IF formula to add other fixed prices for lines after November 2020, so December onwards, so I need have it in order to show some fixed values according my IF formulas, before November and another after december.

I would like to do it, using the same custom column code i posted in my first post, but adding the Month condition.