Issue with Running total shifting previous years data to week 53

[Anonymous]

Hi all,

I have a sales dataset where information is updated each week. The week number in the table has an associated "week start date" which is linked to the "Date" in a date table. (maintains the one-to-many)

My YTD calculations for last year (and all previous years) were working until we adjusted for this year and added a week 53 for 2021. Since then, the data for week1 2020 is being collated as data for week 53, and the running total calculation is adding this phantom week 53 total to the original week 2 number to get a new week 1 total (make sense so far?). BUT  only when I view the running totals by week. The actual weekly sales figures all work fine.

So when I create a cluster chart with weeks on the X axis, and the varying YTD measures in the values, the total for LY YTD and LLY YTD have the total for the second week, listed as the Week 1 total. I;ve added pics of the tablles below to try and help explain.

I have no idea how I can adjust for this. Currently the formula is:

Running Total Retail Sales LY YTD = 
    CALCULATE ([Retail Sales],
                FILTER (ALLSELECTED ('Date_Table'), 'Date_Table'[Date] <= MAX('Date_Table'[Date]) - (365) ),
                     FILTER (ALLSELECTED ('Date_Table'), 'Date_Table'[Date] >= [Wk1 Start LY] )
                        )

Where the [Wk1 Start LY] is:

Wk1 Start LY = Calculate( SELECTEDVALUE( 'Date_Table'[Start]), Filter( ALL('Date_Table') , 'Date_Table'[Wk_Year] = "1_"&Year(Today())-1 ))

Here are a couple of tables showing the running totals, and the week 53 for the previous years that has appeared:

 

Weekly Sales totals:

I hope this is clear enough, but if you need anything further to help me get this right, please let me know! It's driving me crazy right now!

Thanks very much

[AdamBoltryk]

Hi TheoC,

Is any particular reason why you use in your measures logic with var, if your IF make no sense to me?
If (value is zero,
    it's zero,
    if it's not zero it's value).
Adam

[TheoC]

Hi @AdamBoltryk 

There is often situations that arise where you might filter and the output is a nil or blank values. This often happens where filters aren't used in a measure or they create the measure to become too unreadable. By adding the IF... 0 , 0 , else... it provides a basis for the BLANK output to show 0 instead of BLANk. You may see it with KPI or Card visuals if you use them often. It all stems back to filtering on periods or similar filters (I.e. male / female; demographic based filters: divisions; etc) if you are creating dashboards/ reports for 5-10 years worth of data and providing users with control of filtering from start to end of those periods.

Let me know if that makes sense. Happy to explain further.

Great question!

Cheers,

Theo

[Anonymous]

Thanks @TheoC 

I've used a the calculate & Dateadd formula for the YTD when testing and it does work - to a point.

The issue is we work on "weeks", so week 1 for 2020 started on 30/12/2019 - which means all sales for that week are then ignored in the DateAdd calculation, as week 1 is seen as 2019 (as all sales are "dated" based on the week commencing date).

This causes issues when people look at the sales charts and see week 1 had 10,000 units sold in the weekly sales charts, but week 1 YTD is showing zero.

[AdamBoltryk]

Hi pberger15,

In our data model we also use production week logic. We build this as Calendar DIM table with static relation between date and weeks / months. We tried do this as self maintain PBI table, but we failed. The external excel table with calendar updated once a year work for us.

Adam

[TheoC]

@Anonymous , Adam @AdamBoltryk  has raised a great point about the Date table.  I will upload a detailed PBIX dax based model for you both in the morning that may (hopefully) be of use to you both. It runs off calendar year but has also been modified to incorporate financial year end 30 June. Hopefully it will be of assistance. 

[Anonymous]

Thanks @TheoC - I think another issue is that the start of week one sometimes falss in the prior year - so when I adjust for a year, week one falls outside of the scope. I'm thikning of using an "adjusted date" - where week 1 is always the 1st Jan! If that works I think I'll have a small party....

Otherwise, I'll await a more sensible, intelligent and elegant solution!

[TheoC · Accepted solution]

@Anonymous hopefully this helps. It attributes a date to the year it should form part of.  This should help allocate a start and end date for each year according to your dates.   In turn, you can then set Week 1 to align to the AbYearStart column.  Output will be similar to below and the only mainteance is modifying AbYear as / when needed (annually at most).

I applied the following steps:

1. Created a Calculated Column in my Date table for an "abnormal" year start:

AbYear = 

VAR _1 = 'Date'[Date]
VAR _y2019 = DATE ( 2018 , 12 , 17 )
VAR _y2020 = DATE ( 2019 , 12, 30 )
VAR _y2021 = DATE ( 2020 , 12 , 15 )
VAR _y2022 = DATE ( 2021 , 12 , 29 )

RETURN
     SWITCH ( 
          TRUE ( ) ,
             _1 < _y2020 , 2019 ,
             _1 < _y2021 , 2020 ,
             _1 < _y2022 , 2021 ,
             2022 )

2. Created a Start Date column for the AbYear:

AbYearStart = CALCULATE ( MIN ( 'Date'[Date] ) , ALLEXCEPT ( 'Date' , 'Date'[Date] ) )

3. Created an End Date column for the AbYear:

AbYearStart = CALCULATE ( MAX ( 'Date'[Date] ) , ALLEXCEPT ( 'Date' , 'Date'[Date] ) )

Also, for you and @AdamBoltryk, there are two great scripts by Reza and Marco that will cover off on everything you're likely to need in a Date table: https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-bi) and https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/.  

Hope this helps mate.

Theo

[Anonymous]

Thanks @TheoC  - I've marked your solution as accepted - it's far more elegant than the botch that I have forced in to my data.

My botch is a similar idea, in that it adjusts the date the week is linked to - I added a new "date" column in my "weeks" table, that is the "midweek" date - effectively 4 days after the week start date. This is the date I then joined to the date table. 

This leads to every week 1 being linked to the correct year in the date table. I have no doubt I will run into problems in the future - but I needed to get the dashboard up-and-running asap!

Thanks again to you and @AdamBoltryk .

[TheoC]

@Anonymous you're a legend mate. I like the approach you've taken! Best of luck with your Power BI journey big fella!

[TheoC]

Haha @Anonymous love it! 

I provided a solution on the Community not too long ago that had abnormal quarterly periods. I am confident that a similar approach will give you what you're seeking. 

Cheers again and hope to be invited to that small party haha. Love it!

[TheoC]

@Anonymous If you don't have a solution by morning, I'll put something up for you tomorrow. At the moment on phone and it's night here in Australia. 

[TheoC]

Hi @Anonymous 

If you are after cumulative running totals for the complete fiscal / financial year, I'd recommend simplifying both your Current Year and Last Year measures.  In the below, there are three measures:

This is your Retail Sales measure (adjust Table and Amount column to match your data):

m1 Retail Sales = SUM ( Table[Amount] )  

This is the Current Year to Date calculation and should use a Dates / Calendar table where possible:

m2 Retail Sales (CurYr) = 
VAR _1 = CALCULATE ( [m1 Retail Sales] , DATESYTD ( 'Date'[Date] ) )
RETURN
IF ( _1 = 0 , 0 , _1 ) 

This is the Last Year calculation and should use a Dates / Calendar table where possible:

m3 Retail Sales (LstYr) = 
VAR _2 = CALCULATE ( [m1 Retail Sales] , DATEADD ( 'Date'[Date] , -1 , YEAR ) )
RETURN
IF ( _2 = 0 , 0 , _2 ) 

With regard to m3, if you find that the measure does not provide you a cumulative total, adjust to the following:

m3 Retail Sales (LstYr) = 
VAR _2 = CALCULATE ( [m2 Retail Sales (CurYr)] , DATEADD ( 'Date'[Date] , -1 , YEAR ) )
RETURN
IF ( _2 = 0 , 0 , _2 ) 

I hope the above is able to assist. If not, let me know.

All the best!
Theo 🙂