Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

datacode_1-1670519717940.png

 

datacode_1-1670461832314.png

datacode_2-1670461882976.png

 

 

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 

1 ACCEPTED SOLUTION

 @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.

1.png

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

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

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 )
)
)

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.

 

datacode_0-1670519964161.png

 

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 

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 )
)

Hi @tamerj1 ,

Thank you again for sharing updated recommondations. I gave this a try and I have still the same result.

 

I know I thought I understood it too, but I am not sure what could be the issue.

I was thinking the issue was the frequency of the data because the calculation is done over a weekly data and not daily.

@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

Hi @tamerj1,

 

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

Thank you

 @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.

1.png

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

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?

datacode_0-1670602946664.png

 

@datacode 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors