Grouping, powerbi

[Anupahwa]

Hello everyone. I got the requirement in my project . I need to group the quatars based on condition. December to Feb should be quatar1 , then Feb to April quatar 2 and so on ...

To get the solution I created a calculated column in date table then I used switch 

Switch(true(),date calender month number in {12, 1,2},"PTqt1",date calender month number in {3,4,5},"PTQ2"......and I have added this to my date hierarchy .

Now the problem where I got stuck is ,if my client will use a slicer and if they drag year, and the PT quatar which I grouped into a slicer then they are the December month of current year 

Example : if year is selected 2020 and quatar1 so as per my condition it is showing December , Jan ,Feb months data but December should be 2019 , jan and Feb should be of 2020. How can I solve this problem ? 

[Martin_D · Accepted solution]

Hi @Anupahwa ,

The standard pattern to solve this is called "fiscal year" (FY). It's very common, about 1/3 of all companies use it. Fiscal year - Wikipedia

Define with your users what should be the name of the fiscal year:

• The calendar year in which the FY starts or
• The calendar year in which the FY ends or
• The calendar year that matches which the larger proportion of the FY or
• Just name both years, like "FY23/24"

Now add another calculated column calculating the fiscal year name, similar to your custom quarter calculated column, and use the fiscal year in the slicer instead of the calendar year.

See this video for detailed instructions: Calculate Fiscal Year for a Date Dimension using DAX with Power BI (youtube.com)

Kind regards,

Martin

[Anupahwa]

Thanks @Martin_D .It worked👍👍👍

[Martin_D]

Hi @Anupahwa ,

If it worked, would you please be so kind and accept this as solution?

Kind regards,

Martin