topic Re: How to get previous week with an another measure in DAX Commands and Tips

How to get previous week with an another measure

Raneesh — Sun, 13 Mar 2022 07:24:56 GMT

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

)

Re: How to get previous week with an another measure

tamerj1 — Sun, 13 Mar 2022 10:49:33 GMT

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 )

Re: How to get previous week with an another measure

Raneesh — Sun, 13 Mar 2022 10:57:39 GMT

Hi tamerj1 , it returning zero

Re: How to get previous week with an another measure

tamerj1 — Sun, 13 Mar 2022 12:50:05 GMT

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 )

Re: How to get previous week with an another measure

Raneesh — Mon, 14 Mar 2022 04:56:21 GMT

Even now I'm geetting zero.

Thanks for your effort.

Re: How to get previous week with an another measure

tamerj1 — Mon, 14 Mar 2022 06:01:49 GMT

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

Re: How to get previous week with an another measure

Raneesh — Mon, 14 Mar 2022 06:20:04 GMT

Yes.

#Count of customer = var _Customer=DISTINCTCOUNT(leadlist[Unit Number])

return

IF(ISBLANK(_Customer),0,_Customer)

#Occupancy = CALCULATE(SUM('Occupancy tracker'[Total Occupied]))

Re: How to get previous week with an another measure

tamerj1 — Mon, 14 Mar 2022 06:23:48 GMT

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 )

Re: How to get previous week with an another measure

Raneesh — Mon, 14 Mar 2022 06:29:46 GMT

It still shows zero value.

Re: How to get previous week with an another measure

Raneesh — Mon, 14 Mar 2022 06:40:38 GMT

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

Re: How to get previous week with an another measure

Raneesh — Mon, 14 Mar 2022 12:21:32 GMT

tamerj1

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

Re: How to get previous week with an another measure

tamerj1 — Mon, 14 Mar 2022 13:45:02 GMT

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 )

Re: How to get previous week with an another measure

Raneesh — Mon, 14 Mar 2022 13:54:45 GMT

Thank you soo much tamerj1 .

Your extended support resolved my concern. 👍