cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DadGuy09
Frequent Visitor

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

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. Relationships.PNGVisualization1.PNGPOSData.PNG

 

 

 

2 ACCEPTED SOLUTIONS

@DadGuy09 

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

jdbuchanan71_0-1602346107826.png

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.

 

 

View solution in original post

Hi @DadGuy09 , 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.

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User II
Super User II

@DadGuy09 

Set this relationship to bi-directional.

jdbuchanan71_0-1602274338140.png

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

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

 

DayKR YearKR Fiscal WeekBeginningKR Fiscal WeekEndingCY KR Report WKCY Day of Fiscal YearKR PeriodKR QuarterKR Quarter and YearKR Year and PeriodKR Week of PeriodWeek of KR YearKR CYear and WeekSameDateLastYearKR LYear and WeekKR LYearKR LY Report WKKR LY WeekBeginningKR LY WeekEndingKR LY QTRKR LY PeriodCY IRI WeekBeginningCY IRI WeekEndingIRI LY WeekBeginningIRI LY WeekEnding
1/1/2016201512/27/20151/2/20162015  PD 12  WK 4  (48)3491242015  Q42015  PD124482015 48Friday, 2 January, 20152014 4820142014  PD 12  WK 4  (48)12/28/20141/3/20152014  Q42014  PD1212/26/20151/1/201612/27/20141/2/2015

Hi @DadGuy09 , 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.

View solution in original post

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 ) )

@DadGuy09 

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

jdbuchanan71_0-1602346107826.png

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.

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.