Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Greetings all, hopefully you can help me with this one:
I have this measure:
Count of Claims Previous Week_1:= VAR CurrentWeek = SELECTEDVALUE('Claim_LastUpdate'[WeekOfYear])
VAR CurrentYear = SELECTEDVALUE('Claim_LastUpdate'[Year])
VAR MaxWeekNumber = CALCULATE(MAX('Claim_LastUpdate'[WeekOfYear]),ALL(Claim_LastUpdate),'Claim_LastUpdate'[Year]=CurrentYear-1)
VAR BeginPrevWeekValue= CALCULATE(MIN('Claim_LastUpdate'[FirstofWeek]),ALL(Claim_LastUpdate), 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) && Claim_LastUpdate[Year] = CurrentYear )
VAR EndPrevWeekValue= CALCULATE(MAX('Claim_LastUpdate'[LastofWeek]),ALL(Claim_LastUpdate), 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) && Claim_LastUpdate[Year] = CurrentYear )
var result =
IF(CurrentWeek <> 2,
CALCULATE('Claim_Measures'[Count of Claims],FILTER(ALL(Claim_LastUpdate),IF(CurrentWeek=1,Claim_LastUpdate[WeekOfYear]=MaxWeekNumber && Claim_LastUpdate[Year]=CurrentYear-1,Claim_LastUpdate[WeekOfYear] = CurrentWeek - 1 && Claim_LastUpdate[Year]=CurrentYear) )
), IF(CurrentWeek= 2, CALCULATE('Claim_Measures'[Count of Claims],Claim_LastUpdate[FirstOfWeek]=BeginPrevWeekValue && Claim_LastUpdate[LastOfWeek]=EndPrevWeekValue && Claim_LastUpdate[Year] = CurrentYear && 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) ))
)
return result
The reason I am making the distinction with (CurrentWeek=2) is that my calendar table contains only one row for Week 1 of 2019, however there are begin and start dates for this week within this row so I am accomodating that eventuality in retrieving the FirstofWeek and EndofWeek dates in order to calculate 'Claim_Measures'[Count of Claims]. However when rendering in PowerBI, I am getting this:
Obviously I should be getting "32" for "Count of Claims Previous Week_1" measure. When I put the particular calculate expression in DAX studio: i.e.
EVALUATE
{
CALCULATE('Claim_Measures'[Count of Claims],Claim_LastUpdate[FirstOfWeek]= DATE(2018,12,30) && Claim_LastUpdate[LastOfWeek]=DATE(2019,1,5) && Claim_LastUpdate[Year] = 2019 && 'Claim_LastUpdate'[WeekOfYear] = 1)
}
I get the correct results:
Any idea whats going on here? Could it be that the BeginPrevWeekValue and EndPrevWeekValue are including the time also? When rendering the expression for these variables in DAX studio, I do see a date time like here:
EVALUATE
{
CALCULATE(VALUES('Claim_LastUpdate'[FirstofWeek]), 'Claim_LastUpdate'[WeekOfYear] = (1) && Claim_LastUpdate[Year] = 2019 )
}
Thanks in advance for any help!
Solved! Go to Solution.
Found the issue, the measure should be:
Count of Claims Previous Week_1:= VAR CurrentWeek = SELECTEDVALUE('Claim_LastUpdate'[WeekOfYear]) VAR CurrentYear = SELECTEDVALUE('Claim_LastUpdate'[Year]) VAR MaxWeekNumber = CALCULATE(MAX('Claim_LastUpdate'[WeekOfYear]),ALL(Claim_LastUpdate),'Claim_LastUpdate'[Year]=CurrentYear-1) VAR BeginPrevWeekValue= CALCULATE(MIN('Claim_LastUpdate'[FirstofWeek]),ALL(Claim_LastUpdate), 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) && Claim_LastUpdate[Year] = CurrentYear ) VAR EndPrevWeekValue= CALCULATE(MAX('Claim_LastUpdate'[LastofWeek]),ALL(Claim_LastUpdate), 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) && Claim_LastUpdate[Year] = CurrentYear ) var result = IF(CurrentWeek <> 2, CALCULATE('Claim_Measures'[Count of Claims],FILTER(ALL(Claim_LastUpdate),IF(CurrentWeek=1,Claim_LastUpdate[WeekOfYear]=MaxWeekNumber && Claim_LastUpdate[Year]=CurrentYear-1,Claim_LastUpdate[WeekOfYear] = CurrentWeek - 1 && Claim_LastUpdate[Year]=CurrentYear) ) ), IF(CurrentWeek= 2, CALCULATE('Claim_Measures'[Count of Claims],ALL(Claim_LastUpdate),Claim_LastUpdate[FirstOfWeek]=BeginPrevWeekValue && Claim_LastUpdate[LastOfWeek]=EndPrevWeekValue && Claim_LastUpdate[Year] = CurrentYear && 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) )) ) return result
Found the issue, the measure should be:
Count of Claims Previous Week_1:= VAR CurrentWeek = SELECTEDVALUE('Claim_LastUpdate'[WeekOfYear]) VAR CurrentYear = SELECTEDVALUE('Claim_LastUpdate'[Year]) VAR MaxWeekNumber = CALCULATE(MAX('Claim_LastUpdate'[WeekOfYear]),ALL(Claim_LastUpdate),'Claim_LastUpdate'[Year]=CurrentYear-1) VAR BeginPrevWeekValue= CALCULATE(MIN('Claim_LastUpdate'[FirstofWeek]),ALL(Claim_LastUpdate), 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) && Claim_LastUpdate[Year] = CurrentYear ) VAR EndPrevWeekValue= CALCULATE(MAX('Claim_LastUpdate'[LastofWeek]),ALL(Claim_LastUpdate), 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) && Claim_LastUpdate[Year] = CurrentYear ) var result = IF(CurrentWeek <> 2, CALCULATE('Claim_Measures'[Count of Claims],FILTER(ALL(Claim_LastUpdate),IF(CurrentWeek=1,Claim_LastUpdate[WeekOfYear]=MaxWeekNumber && Claim_LastUpdate[Year]=CurrentYear-1,Claim_LastUpdate[WeekOfYear] = CurrentWeek - 1 && Claim_LastUpdate[Year]=CurrentYear) ) ), IF(CurrentWeek= 2, CALCULATE('Claim_Measures'[Count of Claims],ALL(Claim_LastUpdate),Claim_LastUpdate[FirstOfWeek]=BeginPrevWeekValue && Claim_LastUpdate[LastOfWeek]=EndPrevWeekValue && Claim_LastUpdate[Year] = CurrentYear && 'Claim_LastUpdate'[WeekOfYear] = (CurrentWeek -1) )) ) return result
User | Count |
---|---|
85 | |
76 | |
72 | |
69 | |
56 |
User | Count |
---|---|
104 | |
99 | |
92 | |
78 | |
69 |