Time Issues from Weekly Sales Data Import is a Concatenate of Fiscal Period / Week

[Anonymous]

Ok. I'm working on building some simple tables to compare weekly sales by product. My Sales amounts is in Column named 'POS Data'[RETAIL SALES], The import from my retailer sales feed (which operates a 13 period fiscal year which typically begins on the first Sunday of Feb, does not include an actual date, but instead a concatenate of "YYYY  PD ## WK # (XX)" - inside the last parenthesis is the week of the fiscal year. So it would read like "2020  PD 01 W1 (01)" for the first week of the fiscal year. 

I had to create a go-between table RetailerWeek with a single column called "CY KR Report WK" which has all the corresponding retailer weeks to the Calendar Week Ending Dates to eliminate the many to many relationship between the sales report and my CalendarDate table. My CalendarDate table is organized by Day, which includes everything from the associated reporting weekname (described above) Fiscal Year, Fiscal Period, Fiscal Week Beginning, Fiscal Week Ending, Fiscal Quarter, and the corresponding dates of the prior year. 

My sales data is all weekly data and not daily data.

The relationship between the POSData and RetailerWeek intermediary table works great creating any visuals. However, if I try to use the CalendarDate table for time measures (Retailer Yr, Retailer Period, etc.) it will label columns etc but doesn't filter the data. I also want to be able to create new measures for YAG or WOW % etc. using DateAdd or Sameperiodlastyear but BI  won't recognizing my RetailerWeek fields a date or won't pull back the data if I used my CalendarDate table as well. 

Everyone who sees my tables and relationship files all say it looks right and it should work are usually stumped. Any help appreciated. 

[jdbuchanan71 · Accepted solution]

@Anonymous 

Is your date table marked as the date table in the model?

Your measure should only have to reference the date field like this:

Dollars YAG = CALCULATE([Dollars CY],DATEADD('CalendarDate'[DateShort],-364,DAY))

 The extra .[date] if generated by the model automatically when there is not a calendar table.

'CalendarDate'[DateShort].[Date]

With a real date table you will never need to used this extra field.

Also, make sure your date table covers back far enough to include the dates that in the prior year.

[jdbuchanan71]

@Anonymous 

Set this relationship to bi-directional.

That will make it so a filter on the CalendarDate table will be able to flow "up" to the bridge table.

[Anonymous]

That solved my ability to filter based on the time fields from my CalendarDate table. But It still won't return a prior year sum of sales from the same week the prior fiscal year. 

This is my formula for CY Sales

Dollars CY = SUM('POSData'[RETAIL SALES])

This is my current formula for YAG

Dollars YAG = CALCULATE([Dollars CY],DATEADD('CalendarDate'[DateShort].[Date],-364,DAY))

Any thoughts?

Here's the top row or so and headers of my CalendarDate table

Day

KR Year

KR Fiscal WeekBeginning

KR Fiscal WeekEnding

CY KR Report WK

CY Day of Fiscal Year

KR Period

KR Quarter

KR Quarter and Year

KR Year and Period

KR Week of Period

Week of KR Year

KR CYear and Week

SameDateLastYear

KR LYear and Week

KR LYear

KR LY Report WK

KR LY WeekBeginning

KR LY WeekEnding

KR LY QTR

KR LY Period

CY IRI WeekBeginning

CY IRI WeekEnding

IRI LY WeekBeginning

IRI LY WeekEnding

1/1/2016

2015

12/27/2015

1/2/2016

2015  PD 12  WK 4  (48)

349

12

4

2015  Q4

2015  PD12

4

48

2015 48

Friday, 2 January, 2015

2014 48

2014

2014  PD 12  WK 4  (48)

12/28/2014

1/3/2015

2014  Q4

2014  PD12

12/26/2015

1/1/2016

12/27/2014

1/2/2015

[v-jingzhang · Accepted solution]

Hi @Anonymous , it seems that your current formula for YAG Dollars YAG = CALCULATE([Dollars CY],DATEADD('CalendarDate'[DateShort].[Date],-364,DAY)) will return the sales value of a single date a year prior because DATEADD function will get you back to the date a year prior rather than get all the dates between today and that day to calculate the year sum. So you may try DATESINPERIOD or DATESBETWEEN to get the year sum value. 

Examples:

Dollars YAG =
CALCULATE (
    [Dollars CY],
    DATESINPERIOD (
        'CalendarDate'[DateShort].[Date],
        MAX ( 'CalendarDate'[DateShort].[Date] ),
        -364,
        DAY
    )
)

Best Regards,

Community Support Team _ Jing Zhang

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

[Anonymous]

This was a solution to my next issue which was creating rolling periods. Also learned that if I use my YAG sales measure I could calculate the YAG rolling weekly periods as well.  Thanks for your help. See below for L4 and L4 YAG formulas

Dollars L4 = CALCULATE ( [Dollars CY], DATESINPERIOD ( 'CalendarMaster'[DateLong], MAX ( 'CalendarMaster'[DateLong]), -28, DAY ) )

Dollars L4 YAG = CALCULATE ( [Dollars YAG], DATESINPERIOD ( 'CalendarMaster'[DateLong], MAX ( 'CalendarMaster'[DateLong]), -28, DAY ) )

[jdbuchanan71 · Accepted solution]

@Anonymous 

Is your date table marked as the date table in the model?

Your measure should only have to reference the date field like this:

Dollars YAG = CALCULATE([Dollars CY],DATEADD('CalendarDate'[DateShort],-364,DAY))

 The extra .[date] if generated by the model automatically when there is not a calendar table.

'CalendarDate'[DateShort].[Date]

With a real date table you will never need to used this extra field.

Also, make sure your date table covers back far enough to include the dates that in the prior year.