Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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.
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
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 |
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.
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 ) )
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |