powerbi - Power BI Custom Hierarchy for Financial year weekly data

Question

I am working on a dataset that has daily data from 2018 to now. I have to visualize the data set from April-March. The financial year of the company. I also need to drill down the data to a weekly level. I am able to do both these things individually but when I try to combine both these and drill down the data to a weekly level, I get an incorrect result, which as per the logic is correct.

My major problem is that as the data is daily and when a week carryovers to another month the data is distorted as the engine is confused to show in the last month or new month. I think this might be difficult to understand so let me share an example.

For the month of March 2020, it has 5 weeks. The last week begins on 29th March and ends on 4th April. When I drill down my custom hierarchy which has FinancialYr, Qtr and Month and week. It shows me the data as shown below.

It correctly shows the first 4 weeks of April and then carry forwards the last week of the march which begins on the 29th and ends on the 4th of April and shows the data like this. This is also repeated in the following months. As this is a custom hierarchy I can see why this is happening but I'm unable to solve this issue. I think the solution to this could be dropping the last week of the month if it's carry forwarding to the next month, but I'm unable to get this to work.

As I have to show the April-Jun as Qtr1 I had to create a custom hierarchy. To get weekly data I used Week Start Date = Data[Date] – WEEKDAY(Data[Date],1) + 1

If I just use the week start date, I get the weekly data but the quarters are in the calendar format ie Jan-Mar as Q1

question from:https://stackoverflow.com/questions/65918313/power-bi-custom-hierarchy-for-financial-year-weekly-data

Answer 1

Waitting for answers