Two levels of if-else-then

[jeanramonyap]

Basically I am trying to create a custom column using Power Query that if the month is January, then I select last year's Sales data and if it's not, then I use this year's Sales data. The only way I can think of is using 2 levels of if-else but I don't think it's possible. Can anyone recommend an alternative solution to this in Power Query? 

if Date.Month(DateTime.LocalNow()) = 1

then Date.Year([Date]) -1 and [Sales]

else

Date.Year([Date]) and [Sales]

[PhilipTreacy]

Hi @jeanramonyap 

Logically what you are trying to do is possible.  But whether it's achieveable depends on how your data is structured.

The point being, how do you select this year or last year's data?  The data for both years need to be in their own column.  As you haven't supplied any sample data I don't know what your data structure looks like.

Can you please supply some sample data and/or your PBIX file.

Regards

Phil

[jeanramonyap]

Hi Philip,

Thanks for the reply. I can't really give a sample data set but I differentiate the Sales data based on a creation date column. So, now that it's June 2022, the Sales data for current year I select are January to current month 2022 while the Sales data previous year is all of 2021. Let's say it is now, January 2023, I still select all of 2022 as Sales data current year and all of 2021 as Sales data previous year. Currently I am using this DAX expression 

year(SALES[Date])= if(month(today())=1,year(today())-1,year(today())) but I'd like to move this filter in the power query side.

Jean

[v-yiruan-msft]

Hi @jeanramonyap ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can achieve it in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcUgDATQXjhHwuMNU0uU/tv4PyzB16fxwNx3QUVlYpSrQFzKc92FjxlbHybHWGPmNOW0T7PUF0zDPOXIfVg7pq48LFIOiGE9vRvQYaCDwj6PgdwY8xppijZe55Vf47fSViVX3SYmuibbNpDtye0zdVqTT66t33iV7w3EP/f8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dispaly?", each if Date.Year([Date]) = (if Date.Month(DateTime.LocalNow())=1 then Date.Year(DateTime.LocalNow())-1 else Date.Year(DateTime.LocalNow())) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"Dispaly?"] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Dispaly?"})
in
    #"Removed Columns"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[jeanramonyap]

Hi Rena,

Thank you for your reply. However, using this method you filter out the previous year data and I still need this data. I hope to create a column that doesn't filter out the other Sales data.

Regards,

Jean

[v-yiruan-msft · Accepted solution]

Hi @jeanramonyap ,

If you still want to keep the data for previous years, then you can delete the last two steps in Power Query Editor. Only add one custom column, the full applied codes as below(I have updated my sample pbix file, please check the attachment😞

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/JDcUgDATQXjhHwuMNU0uU/tv4PyzB16fxwNx3QUVlYpSrQFzKc92FjxlbHybHWGPmNOW0T7PUF0zDPOXIfVg7pq48LFIOiGE9vRvQYaCDwj6PgdwY8xppijZe55Vf47fSViVX3SYmuibbNpDtye0zdVqTT66t33iV7w3EP/f8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dispaly?", each if Date.Year([Date]) = (if Date.Month(DateTime.LocalNow())=1 then Date.Year(DateTime.LocalNow())-1 else Date.Year(DateTime.LocalNow())) then 1 else 0)
in
    #"Added Custom"

Best Regards

[jeanramonyap]

Hi Rena, thanks for your reply. I modified it appropriately with our data. Thank you for your help!