Forum Discussion

datacode's avatar
datacode
Frequent Visitor
3 years ago
Solved

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...
  • tamerj1's avatar
    tamerj1
    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