March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Tying to get previouse week penetration, I'm getting error. Could you please assist.
Solved! Go to Solution.
Hi @Raneesh
Actually the formula I have messaged you was almost correct, the only mistake is switching between Numerator and Denominator. This one shall work
%Previous Week Penetration =
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
VAR Customers =
CALCULATE (
DISTINCTCOUNT ( leadlist[Unit Number] ),
'Calendar'[Week of Year] = PriorWeek
)
VAR Occupancy =
CALCULATE (
SUM ( 'Occupancy tracker'[Total Occupied] ),
'Calendar'[Week of Year] = PriorWeek
)
RETURN
DIVIDE ( Customers, Occupancy )
Yes.
Then this should work
#Prior Week Penetration =
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
RETURN
CALCULATE (
DIVIDE ( [#Count of customer], [#Occupancy], 0 ),
'Calendar'[Week of Year] = PriorWeek
)
I'm expecting an output like below.
Week Number | 1 | 2 | 3 | ||||
Penetration | Current Week | Previous Week | Current Week | Previous Week | Current Week | Previous Week | |
Building Name | |||||||
Balqis | 4 | 0 | 5 | 4 | 9 | 5 | |
South Residences | 7 | 0 | 8 | 7 | 11 | 8 |
It still shows zero value.
Hi @Raneesh
You can try
#Prior Week Penetration =
VAR _Penetration =
DIVIDE ( [#Count of customer], 'Occupancy tracker'[#Occupancy], 0 )
VAR _PenetrationChecked =
IF ( ISBLANK ( _Penetration ), 0, _Penetration )
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
RETURN
CALCULATE (
_PenetrationChecked,
REMOVEFILTERS ( 'Calendar' ),
'Calendar'[Week of Year] = PriorWeek
)
Hi @Raneesh
seems I forgot to use SUM
#Prior Week Penetration =
VAR Occupancy =
SUM ( 'Occupancy tracker'[#Occupancy] )
VAR _Penetration =
DIVIDE ( [#Count of customer], Occupancy, 0 )
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
RETURN
CALCULATE (
_Penetration,
REMOVEFILTERS ( 'Calendar' ),
'Calendar'[Week of Year] = PriorWeek
)
Hi @Raneesh
Actually the formula I have messaged you was almost correct, the only mistake is switching between Numerator and Denominator. This one shall work
%Previous Week Penetration =
VAR PriorWeek =
MAX ( 'Calendar'[Week of Year] ) - 1
VAR Customers =
CALCULATE (
DISTINCTCOUNT ( leadlist[Unit Number] ),
'Calendar'[Week of Year] = PriorWeek
)
VAR Occupancy =
CALCULATE (
SUM ( 'Occupancy tracker'[Total Occupied] ),
'Calendar'[Week of Year] = PriorWeek
)
RETURN
DIVIDE ( Customers, Occupancy )
Even now I'm geetting zero.
Thanks for your effort.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
16 | |
15 | |
12 | |
11 |
User | Count |
---|---|
32 | |
26 | |
24 | |
20 | |
14 |