Greg
September 12, 2020, 4:20pm
3
There are myriad examples available dealing with non-standard time period analyses. Here are just are a few examples of issues related to the Non-Standard Time Intelligence DAX Pattern from the eDNA resources.
https://forum.enterprisedna.co/t/data-modelling-base-on-custom-time-intelligence-format/1001/2
Hi @Hitman ,
Okay so I think I found something that works but you’ll have to do some checking over multiple years and/or changing start of week days. So without defining custom logic, we’ll rely the standard M function to calculate the Weeknumber.
First Added a new WeekOfYear column with this logic:
Date.WeekOfYear(Date.StartOfWeek([Date],firstDayofWeek),firstDayofWeek)
So the Weeknumber is no longer bound to the [Date] but to the Date.StartOfWeek, this solves the issue where the last days of…
Hi @pedroccamara ,
I’ve added a WeeknYear column to the Dates table. Logic for that column is:
[Year] * 10000 + [Week Number] * 100, type number
So this will work with Custom calendars and your Weekno requirement. Last week sales then becomes:
Sales LW (WeeknYear) =
VAR myWeekOffset = SELECTEDVALUE( Dates[WeeknYear] )
VAR PrevWeek = CALCULATE( MAX( Dates[WeeknYear] ), FILTER( ALL( Dates ), Dates[WeeknYear] < myWeekOffset ))
VAR ListWeeks = SELECTCOLUMNS( ADDCOLUMNS( VALUES( Dates[WeeknYear]…
Hi there,
Yes as you’ve seen unfortunately time intelligence calculations don’t work for fiscal weeks.
Here’s are some videos that talk through how to solve this.
This is the ultimate formula you would need
Previous Year Sales =
VAR CurrentMonth = SELECTEDVALUE( Dates[Week Number] )
VAR CurrentYear = SELECTEDVALUE( Dates[Year] )
RETURN
CALCULATE( [Total Sales],
FILTER( ALL( Dates ),
Dates[Week Number] = CurrentMonth && Dates[Year] = CurrentYear - 1))
