March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
30 | |
25 | |
22 | |
18 |