Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Raneesh
Frequent Visitor

How to get previous week with an another measure

Tying to get previouse week penetration, I'm getting error. Could you please assist.

 

#Prior Week Penetration =
var _Penetration=DIVIDE( [#Count of customer] , 'Occupancy tracker'[#Occupancy] ,0)
return
IF(ISBLANK(_Penetration),0,_Penetration)
var PriorWeek = SELECTEDVALUE ( _Penetration ) -1

RETURN
CALCULATE(
SUM ( _Penetration ),
ALL ( 'Calendar'[Week of Year] ),
'Calendar'[Week of Year] = PriorWeek
)
1 ACCEPTED 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 )

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

@Raneesh 
[#Count of customer] and [#Occupancy] are coulmns or measures?

Yes.

#Count of customer = var _Customer=DISTINCTCOUNT(leadlist[Unit Number])
return
IF(ISBLANK(_Customer),0,_Customer)
 
#Occupancy = CALCULATE(SUM('Occupancy tracker'[Total Occupied]))

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 Number123
PenetrationCurrent WeekPrevious WeekCurrent WeekPrevious WeekCurrent WeekPrevious Week
Building Name       
Balqis 405495
South Residences 7087118

It still shows zero value.

 

Raneesh_0-1647239232542.png

 

tamerj1
Super User
Super User

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 @tamerj1 , it returning zero

 

Raneesh_0-1647168918281.png

 

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
    )

@tamerj1 

 

Do you want to shift both the numerator and the denominator one week back? Yes. exactly.

 

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 )

Thank you soo much @tamerj1 .

Your extended support resolved my concern. 👍

Even now I'm geetting zero.

Thanks for your effort. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.