Including same quarter transactions into fiscal year

[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-lianl-msft]

Hi @Anonymous ,

Could you please share sample data or sample pbix(Please mask any sensitive data before uploading.)

I think your problem can be solved by creating a measure, but it depends on your sample data.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Sorry i'm unable to add it here, but here's a link from google drive.

https://drive.google.com/file/d/1HwebOAQmKWUqar73U8IxCQl78omDE676/view?usp=sharingom/file/d/1HwebOAQ... 

I'm trying to get the data that is shown when the BnsQ option is ticked to remain when the BnSY option is chosen instead. Since being booked and shipped in the same fiscal quarter would also mean that they're booked and shipped in  the same year. However, I still want the three options to exist - BnSQ, BnSY and Normal. Is this possible?

Thank you for your help!

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Because the data affects each other among rows, your idea is good, but we need to consider the principle. You can create a new calculated column, replace bnsq with bnsy, and then continue to filter out bnsq for bnsy.

You can refer to the pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JPY]

Hi,

I'd like to know as well!

Does anyone know??