Forum Discussion
datacode
3 years agoFrequent Visitor
previous week calculation repeating over empty dates weekly to daily relationship
I have the below data model. Where the fact table is weekly and calendar is daily. When I calculate the previous week value it adds all the dates from the calendar rathen than keeping only dates in...
- 3 years ago
datacode
Ok, that was stupidly simple. The SELECTEDVALUE ( Lookup_Calendar[Date] ) has a value in each single date which forces the engine to display the values for all dates. This is supposed to be SELECTEDVALUE ( Data[Date] ). Please refer to attached sample file.PREVIOUSWEEK Value WDT = VAR _Today = SELECTEDVALUE(Data[Date]) VAR _WOT = WEEKNUM( _Today, 1 ) -- Number 2 determines that the week begins on Monday. VAR _LW = _WOT - 1 VAR Result = CALCULATE( SUM( Data[Units]), FILTER( ALLSELECTED( Lookup_Calendar ), Lookup_Calendar[Week Of Year] = _LW && Lookup_Calendar[Year] = YEAR(_Today ) ) ) RETURN Result