Quarter over Quarter when using custom fiscal calendar

[ashok_yadav]

Hello Friends, I am developing a Power BI report and stuck as quarter over quarter comparison.

I have a fiscal calendar year which starts from 1st Feb of every year and ends on 31st Jan, so typically my quarters are Q1 - Feb to Apr, Q2 - May to Jul, Q3 - Aug to Oct and Q4 - Nov to Jan. The last week of quarter breaks into 2 parts if falls into multiple quarter for example if week starts on 4/28 then 3 days of that week falls under Q1 and remaining 4 days will fall under next quarter. So it's not fixed that a quarter will have 13 week or 14 weeks consistently that's why couldn't apply any logical DAX condition based on week number.

NOTE: I have weekly granularity data

I want to compare current QTD with previous QTD sales numbers. For example I have 3 weeks data in current quarter then want to compare with first 3 weeks of previous quarter. I can't use Time Intelligence functions since I am using Fiscal Calendar. Any help will be a great help for me. 

[v-deddai1-msft · Accepted solution]

Hi @ashok_yadav ,

Use the dax below to create fiscal calendar:

FiscalCalendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
    "FsicalYear", IF (
        MONTH ( 'Table'[Date] ) >= 2,
        YEAR ( 'Table'[Date] ),
        YEAR ( 'Table'[Date] ) - 1
    ),
    "FsicalQuartar", IF (
        MONTH ( 'Table'[Date] ) >= 2,
        ROUNDUP ( DIVIDE ( MONTH ( 'Table'[Date] ) - 1, 3 ), 0 ),
        ROUNDUP ( DIVIDE ( MONTH ( 'Table'[Date] ) + 11, 3 ), 0 )
    ),
    "WeekOfFiscal", IF (
        MONTH ( 'Table'[Date] ) >= 2,
        WEEKNUM ( 'Table'[Date] - 31 ),
        WEEKNUM (
            'Table'[Date]
                + IF ( MOD ( 'Table'[Fsical Year], 4 ) = 0, 366 - 31, 365 - 31 )
        )
    ),
    "WeekOfQuarterFiscal", ROUNDUP (
        MOD ( [weeknum by fiscal year], MAX ( [weeknum by fiscal year] ) / 4 ),
        0
    )
)

For QTD by fiscal calendar:

QTD =
CALCULATE (
    SUM ( Fact[Sales] ),
    FILTER (
        ALLEXCEPT ( FiscalCalendar, FiscalCalendar[FiscalYear] ),
        FiscalCalendar[WeekOfQuarterFiscal]
            <= MAX ( FiscalCalendar[WeekOfQuarterFiscal] )
            && FiscalCalendar[FiscalQuartar] = MAX ( FiscalCalendar[FiscalQuartar] )
    )
)

PreQTD =
CALCULATE (
    SUM ( Fact[Sales] ),
    FILTER (
        ALLEXCEPT ( FiscalCalendar, FiscalCalendar[FiscalYear] ),
        FiscalCalendar[WeekOfQuarterFiscal]
            <= MAX ( FiscalCalendar[WeekOfQuarterFiscal] )
            && FiscalCalendar[FiscalQuartar] = MAX ( FiscalCalendar[FiscalQuartar] )-1
    )
)

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

Best Regards,

Dedmon Dai

[amitchandak]

@ashok_yadav , refer for calendar

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

For QTD - with or without time intelligence

https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839