topic Re: previous week calculation repeating over empty dates weekly to daily relationship in DAX Commands and Tips

previous week calculation repeating over empty dates weekly to daily relationship

datacode — Fri, 09 Dec 2022 00:41:00 GMT

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

Re: previous week calculation repeating over empty dates weekly to daily relationship

tamerj1 — Thu, 08 Dec 2022 03:28:19 GMT

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

Re: previous week calculation repeating over empty dates weekly to daily relationship

datacode — Thu, 08 Dec 2022 17:20:36 GMT

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

Re: previous week calculation repeating over empty dates weekly to daily relationship

tamerj1 — Thu, 08 Dec 2022 18:20:12 GMT

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

Re: previous week calculation repeating over empty dates weekly to daily relationship

datacode — Thu, 08 Dec 2022 18:36:29 GMT

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.

Re: previous week calculation repeating over empty dates weekly to daily relationship

tamerj1 — Thu, 08 Dec 2022 19:41:38 GMT

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

Re: previous week calculation repeating over empty dates weekly to daily relationship

datacode — Fri, 09 Dec 2022 00:42:56 GMT

Hi tamerj1,

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

Thank you

Re: previous week calculation repeating over empty dates weekly to daily relationship

tamerj1 — Fri, 09 Dec 2022 08:59:02 GMT

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

Re: previous week calculation repeating over empty dates weekly to daily relationship

datacode — Fri, 09 Dec 2022 16:22:36 GMT

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?

Re: previous week calculation repeating over empty dates weekly to daily relationship

tamerj1 — Fri, 09 Dec 2022 17:36:17 GMT

datacode

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