How do I calculate this year fiscal week vs last year fiscal week?

DAX DAX Calculations
1

Hello.

I have safety data. I’m trying to count total accidents this year vs SPLY.

Currently, I was using

Calculate ( [Total Acc], sameperiodlastyear([datecolumn]))

This works fine, I believe it will count all records through the same number of days last year.

Problem I encounter is when I want to do it by fiscal week. Week 1 vs week 1 last year, the calendar year dates aren’t aligned.
Not sure what kind of pattern I can use. Hopefully my issue is clear, can anyone assist?

2

Hi @tigerec,

For that you will need a proper Dates table that contains all Fiscal date attributes and a pattern that looks something like this:

Accidents Fiscal Week CY =
VAR cFY = MAX( 'Dates'[Fiscal Year] )
VAR cFW = MAX( 'Dates'[Fiscal Week] )
RETURN
    CALCULATE( [Total Acc],
        FILTER( ALL( 'Dates' ),
            'Dates'[Fiscal Year] = cFY  &&
            'Dates'[Fiscal Week] = cFW
        )
    )

And for the previous year

Accidents Fiscal Week LY =
VAR cFY = MAX( 'Dates'[Fiscal Year] )
VAR cFW = MAX( 'Dates'[Fiscal Week] )
RETURN
    CALCULATE( [Total Acc],
        FILTER( ALL( 'Dates' ),
            'Dates'[Fiscal Year] = cFY - 1 &&
            'Dates'[Fiscal Week] = cFW
        )
    )

For the comparison you can subtract one measure from the other.
I hope this is helpful

2 Likes
3

Thank you so much. This worked, however my card is blank, no total.

Should I use HASONEFILTER ON Dates[Date] pattern or you recommend something else?

4

Hi @tigerec,

@BrianJ did a video on fixing Card visuals you might find helpful.

5

Hi @tigerec, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.