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...
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
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

datacode 

If this is a sample file can you share it? Or otherwise, can you create a similar sample file that simulates the issue and share it with me?

thsnk you

datacode's avatar
datacode
Frequent Visitor
3 years ago

Hi tamerj1,

 

I have edited the post to a pbix file sample with the same issue.

Thank you

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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
    • datacode's avatar
      datacode
      Frequent Visitor
      3 years ago

      Hello tamerj1 ,

      So you are so helpful thank you so much for your help this worked!!!

      Question what option do you use to insert the pbix file without going to an external link?

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        datacode 

        Unfortunately, this option is available only for superusers and community service