Including Same Quarter Deliveries in Same Year Deliveries

[Anonymous]

Hi,

I've come up with a conditional column that states if orders are booked and shipped in the same fiscal quarter (BnSQ) ,in the same fiscal year (BnSY) or normal(not in the same quarter or year). I've placed a slicer that allows the user to filter according to these three variables. The ones that are allocated BnSQ are accurate, but they dont show up when BnSY is selected. This should be the case since those booked and shipped in the same FQ, would be also booked and shipped in the same FY as well.  I understand that this is because i've created a column and the respective variables have been assigned according to the condition.

What i've done is:
- Create a conditional column known as "Op3" which distinguishes deals booked and shipped within the same FQ

- Create a conditional column known as "Op4" which distinguishes deals that are executed within the same FY

- Create a conditional column to assign the variable names (BnSQ,BnSY and normal) based on a few conditions.

Here's my code that can be found in the Advanced Editor:

   Op3 = Table.AddColumn(#"Changed Type7", "Op3", each if [Delivery Q] = [FQ] then 1 else if [Delivery Q] <> [FQ] then 2 else null),
    #"Changed Type6" = Table.TransformColumnTypes(Op3,{{"Op3", Int64.Type}, {"Delivery Year", type text}}),
    Op4 = Table.AddColumn(#"Changed Type6", "Op4", each if [FY] = [Delivery Y] then 1 else 0),
    #"BnS - Q&Y" = Table.AddColumn(Op4, "BnS - Q&Y", each if [Op3] = [Op4] then "BnSQ" else if [Op4] = 1 then "BnSY" else "Normal")

Any idea how i can go about solving this issue?

Thank you

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could create a new column to let "BnSQ" and "BnSY" be the same value.

#"Custom" = Table.AddColumn(#"BnS - Q&Y", "Custom", each if [BnS - Q&Y] = "BnSQ" then 1 else if [BnS - Q&Y] = "BnSY" then 1 else 0)

Then you could create a new table which has slicer element and its value. Use a measure as a filter to show the right result.

Here is my test file for your reference.