How can I get the 53 week last year to appear?

DAX DAX Calculations
1

I feel this question would apply to others but I have searched for days on the web and not found an answer.

We use an ISO calendar.
Each year has 13 x 4 week periods and, every six or seven years we have a leap year with 53 weeks.

How can I get the last year’s sales for week 53 (FP 13 Week 5) to appear and be included in the totals?

The lack of a week 53 in the current year is the cause, but can anyone help me to remediate it?

Thanks in anticipation.

Bill

image
image232×675 31.7 KB

1 Like
2

@billlee ,

In your matrix row field properties, make sure “Show items with no data” option is turned on.

image

If that doesn’t solve the problem, please post your work in progress PBIX file and I’m sure we can get the problem worked out.

I hope this is helpful.

  • Brian
1 Like
3

Thanks, Brian, I have tried this and it doesn’t work.
I assume because there are no dates for this week in the current year.
I did upload a sample pbix file, are you able to see this?

4

Time Intelligence.pbix (830.0 KB)

Just in case, here is the file again

5

just a fyi…leap year occurs every 4 year not 6 to 7 year

6

Ours is a leap week, an extra 7 days in the leap year.
So say 6 years of 52 weeks then one year with 53 weeks

7

never heard of being called leap week but i guess its related to ISO calendar. I have no experience using the iso calendar

8

are you using the extended date table that Melissa set up within forum?

This is ISO calendar information in it that might make your calculation easier.

thanks
Keith

9

@billlee ,

I think I got you most, but not all, of the way down the road to the solution you’re looking for. If you look at my revised Net Sales LY measure, I believe it’s returning the correct values for all FYs and FPs now (yours goes sideways after the leap week).

image
image819×530 65.4 KB 

Net Sales LY Revised = 

VAR CurrFY = SELECTEDVALUE( Dates[FiscalYear] )

VAR Result = 
CALCULATE(
    [Net Sales],
    FILTER(
        ALL( Dates[FiscalYear], Dates[FiscalYearName], Dates[FiscalPeriod]),
        Dates[FiscalYear] = CurrFY - 1
    )
)

RETURN Result

The one thing I just can’t get to work is having the leap week show up in the Net Sales LY Revised column, even though the total for that FP is now correct.

I hope this is helpful. Full solution file attached.

P.S. Not marking you date table as a date table makes a big difference in this case. Here’s a good explanation of why:

10

Thanks Brian - that’s a lot of help. The leap week not showing up is really frustrating and has caused me many wasted hours - I can’t accept there isn’t a way? Or maybe I’m just too stubborn.

11

thanks Keith - I am using an extended ISO date table but it doesn’t help. We inherited this calendar from Burger King!

12

I hear you. Let me take a fresh look at it in the morning and see if I can crack it. If not, I’ll kick it to the expert team and see if one of them can.

  • Brian
1 Like
13

@billlee ,

Working on it as we speak - got pulled into some other stuff this AM.

  • Brian
14

@billlee ,

Ha! This was a tough SOB, but I finally cracked it.

Combination of data modeling changes and DAX. Added the following table to the data model connected by an inactive relationship.

image
image502×613 21.9 KB

If the relationship in either measure is inactive, it forces the fifth row to remain. Even though both measures turn the inactive relationship on via USERELATIONSHIP, the visual treats them independently so while both measures are calculated correctly, the visual acts as though the relationship in one of the measures is turned off, thus materializing the fifth row.

image

I hope this gets you what you need. Full revised solution file attached below.

– Brian
eDNA Forum - Time Intelligence Solution 2.pbix (833.3 KB)

1 Like
15

Brilliant, thanks.
Brian this has been really helpful and a massive timesaver :grinning:

1 Like
16

@billlee,

Thanks – glad to hear that. This was a really fun and challenging one – enjoyed working with you on it.

  • Brian