This year vs previous year sales non standard 445 calendar

[Anonymous]

I’m having an same issue showing this year vs previous year units sold using a custom 445 fiscal calendar. I’d like to be able to show current vs previous year sales within a hierarchy (year, quarter, month, week). 

I’ve watched some of the videos and tutorials online. I was able to successfully look at a single granularity of data (current week vs same week last year, or current quarter vs same quarter last year), however when I try to place these aggregations in a hierarchy on a table or bar chart, it doesn’t work. The totals show up blank.

I’ve attached a sample pbix file which contains a simple data model with my 445 date table (the fiscal dates are labeled FiscalYear, FiscalQuarter, etc.)., plus a transaction table (units sold = sum[units sold]. I’m hoping someone can show me how to create a dax formula that allows the user to view current vs previous data within a hierarchy based on the fiscal calendar.

So far, I’ve tried the following dax, which work only in one granularity: either year OR week, but I can’t stack these into a hierarchy to move up and down the date hierarchy. This version works with current vs previous year, but not the other levels of the date hierarchy:

Units Sold Previous FY = CALCULATE([Units Sold], filter(all(dates), dates[fiscalyear]=MAX(Dates[FiscalYear])-1) )

This next bit of DAX  works for comparing this week vs the same week last year, but you can’t drill up from week to month to quarter to year, as it returns blanks when you leave the week level of the hierarchy:

Units Sold PY Fiscal 1 =
var currentfiscalweek = SELECTEDVALUE(DatesPrim[FiscalWeeknum])
var currentfiscalyear = selectedvalue(DatesPrim[FiscalYear])

return

CALCULATE(’+Measures’[Units Sold],
FILTER(ALL(datesprim),
DatesPrim[FiscalWeeknum] = currentfiscalweek &&
DatesPrim[FiscalYear] = currentfiscalyear - 1) )

Thank you for your help! 

[kaisrec]

When working with fiscal calendar, such as the popular 455 calendar used in retail and finance comparing data year-over-year requires more than just shifting dates by 12 months. The 455 calendar divides the year into quarters of 4, 5, and 5 weeks, which means periods do not align exactly with standard calendar months.
To make meaningful comparisons, especially for prior year analysis, it’s essential to align the previous year’s data only to the periods that exist in the latest year.

If you simply compare the previous year’s total or period values without alignment, you might compare periods that don’t exist or have mismatched lengths, leading to inaccurate or misleading insights.
For example, if your latest year has data only up to Period 8, you want your previous year comparison to also consider only Periods 1 to 8, ignoring any data beyond that. This ensures an apples-to-apples comparison and helps identify true performance trends.

[PreviousYearAligned] =
    VAR _MaxYearOverall = MAXX( ALL( 'Table' ), 'Table'[Year] )
    VAR _MaxYearCurrent = MAX( 'Table'[Year] )
    VAR _LatestYearPeriods =
        CALCULATETABLE(
            VALUES( 'Table'[Period] ),
            'Table'[Year] = _MaxYearOverall
        )
    RETURN
        CALCULATE(
            SUM( 'Table'[Amount] ),
            FILTER(
                ALL( 'Table'[Year] ),
                'Table'[Year] = _MaxYearCurrent - 1
            ),
            _LatestYearPeriods
        )

[amitchandak]

@Anonymous , I have created a 4,4,4 calendar and there I created a year Number(same logic you can use) that can be used for time intelligence the same you have to use for this year vs last year or you have trailing 364 days

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

using the year no of 445 calendar

DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

refer custom period time intelligence, jump using chapter

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs