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 the fact table.

 

 

 

PREVIOUSWEEK Value WDT = 
VAR _Today =
    SELECTEDVALUE(Lookup_Calendar[Date])
VAR _WOT =
    WEEKNUM( _Today, 1 ) -- Number 2 determines that the week begins on Monday.
VAR _LW = _WOT - 1
RETURN
    CALCULATE(
        SUM( Data[Units]),
        FILTER(
            ALLSELECTED( Lookup_Calendar ),
            Lookup_Calendar[Week Of Year] = _LW
                && Lookup_Calendar[Year] = YEAR(_Today )
        )
    )

 

pbixfile 

Data model
DAX
relationships
Time Intelligence
WOW
  • 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

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi datacode 

    please try

    PREVIOUSWEEK Value WDT =
    VAR _Today =
    SELECTEDVALUE ( Lookup_Calendar[Date] )
    VAR _WOT =
    WEEKNUM ( _Today, 1 ) -- Number 2 determines that the week begins on Monday.
    VAR _LW = _WOT - 1
    VAR SelectedDates =
    CALCULATETABLE (
    ALLSELECTED ( Lookup_Calendar ),
    CROSSFILTER ( Lookup_Calendar[Date], Data[Date], BOTH )
    )
    RETURN
    CALCULATE (
    SUM ( Data[Units] ),
    FILTER (
    SelectedDates,
    Lookup_Calendar[Week Of Year] = _LW
    && Lookup_Calendar[Year] = YEAR ( _Today )
    )
    )

    • datacode's avatar
      datacode
      Frequent Visitor
      3 years ago

      Hi tamerj1,

      Thank you for your response and taking the time. I thought this would of work it was a good idea.

      Unfortunately, I am still receiving the same output. I find it strange this is not been filtered out.

       

       

      PREVIOUSWEEK Value WDT = 
VAR _Today =
SELECTEDVALUE ( Lookup_Calendar[Date] )
VAR _WOT =
WEEKNUM ( _Today, 1 ) -- Number 2 determines that the week begins on Monday.
VAR _LW = _WOT - 1
VAR SelectedDates =
CALCULATETABLE (
ALLSELECTED ( Lookup_Calendar ),
CROSSFILTER ( Lookup_Calendar[Date], Data[Date], Both)
)
RETURN
CALCULATE (
SUM ( Data[Units] ),
FILTER (
SelectedDates,
Lookup_Calendar[Week Of Year] = _LW
&& Lookup_Calendar[Year] = YEAR ( _Today )
)
)

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        datacode 

        Interesting. It seems that I don't understand this behavior 😅

        however please try

        PREVIOUSWEEK Value WDT =
        VAR _Today =
        SELECTEDVALUE ( Lookup_Calendar[Date] )
        VAR _WOT =
        WEEKNUM ( _Today, 1 ) -- Number 2 determines that the week begins on Monday.
        VAR _LW = _WOT - 1
        VAR DatesWthData =
        ALL ( Data[Date] )
        RETURN
        CALCULATE (
        SUM ( Data[Units] ),
        FILTER (
        ALLSELECTED ( Lookup_Calendar ),
        Lookup_Calendar[Week Of Year] = _LW
        && Lookup_Calendar[Year] = YEAR ( _Today )
        && Lookup_Calendar[Date] IN DatesWthData
        )
        )

         

        *EDIT

        In general ALLSELECTED as a table function can be complex and sometimes it's difficult to understand its behaviour. It also could be just as simple as

        PREVIOUSWEEK Value WDT =
        VAR _Today =
        SELECTEDVALUE ( Lookup_Calendar[Date] )
        VAR _WOT =
        WEEKNUM ( _Today, 1 ) -- Number 2 determines that the week begins on Monday.
        VAR _LW = _WOT - 1
        RETURN
        CALCULATE (
        SUM ( Data[Units] ),
        ALLSELECTED ( Lookup_Calendar ),
        Lookup_Calendar[Week Of Year] = _LW
        && Lookup_Calendar[Year] = YEAR ( _Today )
        )