Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 )
)
)
Solved! Go to 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.
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
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.
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 )
)
)
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.
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
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
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?
Unfortunately, this option is available only for superusers and community service
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |