Solved: Re: How to achive below looping logic in powerbi

TriveniN · ‎04-19-2023

Need Help on below requirement

Based on slicer selection

Example if user selects dates between 1/1/2023 to 01/31/2023

we need to calculate count of property's like below

for 01/01/2023 :

count(name), listing_start_date <mindate

and it should be divided by no.of days, here in this case no.of days is 1

for 02/01/2023 : listing_start_date <mindate+1

and it should be divided by no.of days, here in this case no.of days is 2

etc..... and we need to sum up all and show the result like below

Proprty_name SUM_Value

Atlanta 30

Any ideas would be largely appreciated.

lbendlin · ‎04-26-2023

Something like this?

You can use averagex() instead of sumx() for the final result.

see attached.

View solution in original post

TriveniN · ‎05-05-2023

How to get occupency only for few properties

Below is DAX to Calculate count of leases and it will return 468

VAR _SS_AvgOcc =

CALCULATE( DISTINCTCOUNT(LEASE_SFV[Name 1] ),

LEASE_SFV[Lease_Type__c] IN {"Initial"}

&&

NOT LEASE_SFV[Yardi_Lease_Status__c] IN {"Canceled" , "pending"}

,

FILTER(ACQUIRED_PROPERTY_SFV,

(ACQUIRED_PROPERTY_SFV[Stabilization_Date__c] <> BLANK() &&

ACQUIRED_PROPERTY_SFV[Stabilization_Date__c] <= _SSdate ) --

&& (ACQUIRED_PROPERTY_SFV[Disposition_Date__c] = BLANK() ||

ACQUIRED_PROPERTY_SFV[Disposition_Date__c] > _SSdate) )

)

I need to calculate occupency only for above properties(468). Below is the DAX to calculate occupency

VAR _SS_AvgOcc_Quarter =

CALCULATE( IF(

MAXX(LEASE_SFV,LEASE_SFV[Move_Out_Date__c]) < _date

&& VALUES(LEASE_SFV[Move_Out_Date__c])<> BLANK() ,

DATEDIFF( _dateQMIN , MAXX(LEASE_SFV,LEASE_SFV[Move_Out_Date__c]), DAY) ,

DATEDIFF( _dateQMIN , _dateQ +1, DAY)

),

LEASE_SFV[Lease_Type__c] = "Initial" &&

NOT LEASE_SFV[Yardi_Lease_Status__c] IN {"Canceled" , "pending"}

&&

IF(SELECTEDVALUE(ReportPeriod[Type]) = "Month",

FORMAT(LEASE_SFV[Lease_Start_Date__c],"YYYYMM") <= FORMAT(_date,"YYYYMM"), --date

IF(SELECTEDVALUE(ReportPeriod[Type]) = "Quarter",

FORMAT(LEASE_SFV[Lease_Start_Date__c],"YYYYQ") <= FORMAT(_date,"YYYYQ")) )

&&

( LEASE_SFV[Move_Out_Date__c] = BLANK() ||

IF(SELECTEDVALUE(ReportPeriod[Type]) = "Month",

FORMAT(LEASE_SFV[Move_Out_Date__c],"YYYYMM") >= FORMAT(_dateMIN,"YYYYMM"),

IF(SELECTEDVALUE(ReportPeriod[Type]) = "Quarter",

FORMAT(LEASE_SFV[Move_Out_Date__c],"YYYYQ") >= FORMAT(_dateMIN,"YYYYQ"))))

,

FILTER(ACQUIRED_PROPERTY_SFV,

(ACQUIRED_PROPERTY_SFV[Stabilization_Date__c] <> BLANK() &&

ACQUIRED_PROPERTY_SFV[Stabilization_Date__c] <= _SSdate )

&& (ACQUIRED_PROPERTY_SFV[Disposition_Date__c] = BLANK() ||

ACQUIRED_PROPERTY_SFV[Disposition_Date__c] > _date) )

)

VAR _TPDIP =

IF( SELECTEDVALUE(ReportPeriod[Type])="Month",

DATEDIFF(CALCULATE(STARTOFMONTH(Datecalendar[TheDate])), _dateM+1,DAY),

IF(SELECTEDVALUE(ReportPeriod[Type])="Quarter",

DATEDIFF(CALCULATE(STARTOFQUARTER(Datecalendar[TheDate])), _dateQ+1, DAY)) )

VAR _SS_AvgOcc= IF(SELECTEDVALUE(ReportPeriod[Type]) = "Month",_SS_AvgOcc_Month,_SS_AvgOcc_Quarter)

RETURN

SWITCH( TRUE(),

SELECTEDVALUE(TotalProtfolio_Rows[Key]) = 1 && SELECTEDVALUE(ReportType[Key]) = 1 ,

_TP_AvgOcc/_TPDIP,

SELECTEDVALUE(TotalProtfolio_Rows[Key]) = 1 && SELECTEDVALUE(ReportType[Key]) = 2

// && _SS_AvgOcc_Name <> BLANK()

,

_SS_AvgOcc/_TPDIP,

BLANK())

When we use Occupency DAX(above DAX) , it is calculating occupency for 679 leases(Total leases 679)

How to calculate occupency only for those 468 leases?

Any ideas would be largely appreciated.

lbendlin · ‎05-05-2023

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

lbendlin · ‎04-23-2023

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

TriveniN · ‎04-26-2023

Thanks for your response and below table is for sample data.

MSA	County	LstStartDate__c	LstEnd_Date__c	MIR_End_Date__c	MType__c	MR_Start_Date__c	Yar_Date__c
Jak	Clay	9/21/2021	12/10/2021	10/19/2021	Initial	8/17/2021	12/17/2021
Jak	Clay	4/12/2023		4/12/2023	Turn	4/3/2023
Dal	Collin						11/3/2021
Dal	Collin	12/23/2021	3/7/2022	12/21/2021	Initial	11/8/2021	3/10/2022
Dal	Collin	4/18/2023		4/24/2023	Turn	4/11/2023
Dal	Denton	12/20/2021	3/7/2022	12/16/2021	Initial	11/8/2021	3/13/2022
Dal	Denton	4/19/2023		4/20/2023	Turn	4/18/2023
Atl	Gwi	12/27/2021	5/7/2022	12/24/2021	Initial	10/28/2021	5/20/2022
Atl	Gwi	3/20/2023		3/24/2023	Turn	3/17/2023
Atl	Gwi	8/26/2022		8/26/2022	Initial	8/5/2022
Jak	Clay	11/3/2021	11/12/2021	11/3/2021	Initial	9/27/2021	11/12/2021
Jak	Clay	3/31/2022	4/5/2022	3/30/2022	Turn	3/23/2022	4/8/2022
Jak	Clay	4/12/2023		4/18/2023	Turn	4/3/2023
Jak	Clay	1/20/2022	1/27/2022	1/20/2022	Initial	11/9/2021	2/9/2022
Jak	Clay	2/23/2023		2/27/2023	Turn	2/20/2023
Atl	Cobb	10/4/2021	12/6/2021	10/1/2021	Initial	8/3/2021	12/10/2021
Atl	Cobb	3/30/2023		4/4/2023	Turn	3/21/2023
Dal	Collin	9/29/2021	12/9/2021	9/28/2021	Initial	7/29/2021	12/15/2021
Dal	Collin	3/22/2023		4/12/2023	Turn	3/20/2023
Dal	Collin						3/14/2022
Dal	Collin	4/20/2022	8/27/2022	5/13/2022	Initial	4/1/2022	8/31/2022
Dal	Collin	4/18/2023		4/24/2023	Turn	4/10/2023
Dal	Denton	3/26/2021	4/17/2021	2/10/2021	Initial	1/25/2021	3/30/2021
Dal	Denton	3/4/2022	3/23/2022	3/2/2022	Turn	1/27/2022	3/28/2022
Dal	Denton	4/14/2023		4/19/2023	Turn	4/11/2023
Dal	Denton	7/22/2021	10/22/2021	7/21/2021	Initial	6/4/2021	10/30/2021
Dal	Denton	4/19/2023		4/21/2023	Turn	4/14/2023
Jak	Duval	7/14/2021	9/27/2021	9/16/2021	Initial	6/21/2021	9/30/2021
Jak	Duval	12/30/2022		1/20/2023	Turn	12/19/2022
Jak	Duval	1/10/2022	2/23/2022	12/30/2021	Initial	11/3/2021	2/26/2022
Jak	Duval	3/10/2023		4/3/2023	Turn	3/3/2023
Dal	Ellis	8/16/2021	8/26/2021	8/26/2021	Initial	7/9/2021	8/31/2021
Dal	Ellis	4/10/2023		4/16/2023	Turn	3/31/2023
Dal	Ellis						8/13/2021
Dal	Ellis	11/29/2021	2/11/2022	11/29/2021	Initial	9/7/2021	2/13/2022
Dal	Ellis	4/18/2023		4/25/2023	Turn	4/10/2023
Atl	Gwi	1/4/2022	2/14/2022	12/31/2021	Initial	8/19/2021	2/20/2022
Atl	Gwi	2/23/2023		2/28/2023	Turn	2/20/2023
Chr	Iredell	1/31/2022	5/10/2022	1/29/2022	Initial	10/26/2021	5/12/2022
Chr	Iredell	2/13/2023		2/23/2023	Turn	2/1/2023
Dal	Kaufman	9/21/2021	1/6/2022	9/20/2021	Initial	7/2/2021	1/16/2022
Dal	Kaufman	3/3/2023		3/13/2023	Turn	2/14/2023
Jak	St. Johns	3/25/2022	5/10/2022	3/24/2022	Initial	2/24/2022	5/15/2022
Jak	St. Johns	12/6/2022		12/13/2022	Turn	12/5/2022
Jak	St. Johns	4/1/2022	5/9/2022	4/1/2022	Initial	2/22/2022	5/17/2022
Jak	St. Johns	3/14/2023		3/17/2023	Turn	3/13/2023
Dal	Tarrant	4/22/2021	5/2/2021	4/20/2021	Initial	3/17/2021	5/12/2021
Dal	Tarrant	6/24/2022	6/29/2022	6/24/2022	Turn	6/1/2022	7/11/2022
Dal	Tarrant	4/10/2023		4/12/2023	Turn	4/4/2023
Dal	Denton						5/9/2022
Dal	Denton	12/8/2022		12/17/2022	Initial	11/30/2022
Atl	Gwi	12/5/2022		12/12/2022	Initial	11/17/2022
Chr	Iredell	9/18/2022		10/6/2022	Initial	9/9/2022
Chr	Iredell	10/13/2022		10/21/2022	Initial	10/3/2022
Dal	Kaufman	9/6/2022	11/1/2022	9/8/2022	Initial	8/26/2022	11/3/2022
Dal	Kaufman	3/1/2023		3/8/2023	Turn	2/28/2023
Chr	Mecklenburg	5/13/2022	6/27/2022	5/31/2022	Initial	4/19/2022	6/29/2022
Chr	Mecklenburg	11/11/2022		11/21/2022	Turn	11/3/2022
Jak	St. Johns	9/30/2022	11/13/2022	10/14/2022	Initial	8/29/2022	11/14/2022
Jak	St. Johns	4/14/2023		4/18/2023	Turn	4/3/2023
Chr	Union	9/29/2022		10/6/2022	Initial	9/12/2022
Atl	Forsyth	1/1/2022	2/5/2022	12/29/2021	Initial	9/27/2021	2/10/2022
Atl	Forsyth	4/18/2023		4/25/2023	Turn	4/10/2023
Atl	Forsyth						9/9/2021
Atl	Forsyth	2/4/2022	2/10/2022	2/2/2022	Initial	9/28/2021	2/16/2022
Atl	Forsyth	2/27/2023		3/6/2023	Turn	2/22/2023
Jak	Nassau	12/17/2021	4/25/2022	12/15/2021	Initial	10/14/2021	4/27/2022
Jak	Nassau	3/21/2023		3/22/2023	Turn	3/15/2023
Jak	St. Johns	6/2/2022	9/12/2022	6/6/2022	Initial	5/25/2022	9/21/2022
Jak	St. Johns	3/20/2023		3/24/2023	Turn	3/16/2023
Chr	Cabarrus	2/28/2022	6/6/2022	2/23/2022	Initial	11/20/2021	6/7/2022
Chr	Cabarrus	4/14/2023		4/24/2023	Turn	4/9/2023
Dal	Ellis						11/30/2021
Dal	Ellis	2/11/2022	3/7/2022	2/10/2022	Initial	12/29/2021	3/15/2022
Dal	Ellis	11/9/2022	12/14/2022	11/9/2022	Turn	11/2/2022	12/19/2022
Dal	Ellis	3/8/2023		3/13/2023	Turn	3/6/2023
Chr	Gaston						7/5/2022
Chr	Gaston	8/9/2022	9/16/2022	8/20/2022	Initial	8/2/2022	9/20/2022
Chr	Gaston	4/3/2023		4/6/2023	Turn	4/1/2023
Atl	Gwi	7/22/2022	9/18/2022	7/29/2022	Initial	6/23/2022	9/22/2022
Atl	Gwi	3/3/2023		3/9/2023	Turn	2/28/2023
Chr	Iredell	10/14/2021	4/5/2022	1/8/2022	Initial	9/20/2021	4/5/2022
Chr	Iredell	4/18/2023		4/22/2023	Turn	4/10/2023
Chr	Iredell	4/12/2022	4/29/2022	4/11/2022	Initial	3/21/2022	5/3/2022
Chr	Iredell	11/30/2022		12/14/2022	Turn	11/29/2022
Chr	Lincoln						4/25/2022
Chr	Lincoln	7/1/2022	7/26/2022	7/19/2022	Initial	6/9/2022	7/28/2022
Chr	Lincoln	1/3/2023		1/6/2023	Turn	12/27/2022
Dal	Rockwall	12/28/2021	3/28/2022	12/23/2021	Initial	11/10/2021	4/1/2022
Dal	Rockwall	4/18/2023		4/19/2023	Turn	4/13/2023

I have DAX calculation to get data till ASof date and Below is the DAX to get ASOF date.

P Listing =

VAR _Date = CALCULATE(MAX(Datecalendar[TheDate]))

RETURN

CALCULATE( COUNT(C_TIME[Name]),

C_Time[LstStartDate__c] <= _Date && C_Time[LstStartDate__c] <> Blank() &&

(C_Time[LstEnd_Date__c] = Blank() || C_Time[LstEnd_Date__c] > _Date) &&

C_TIME[MIR_End_Date__c] <> BLANK() && C_TIME[MIR_End_Date__c] <= _Date &&

C_TIME[MType__c] <> "Disposition" ,

C_TIME[MR_Start_Date__c] <> BLANK() &&

(C_Time[Yar_Date__c] = BLANK() || C_Time[Yar_Date__c] > _Date))

Requirment : Based on slicer selection

Example if user selects Calendar dates between 22/04/2023 to 25/04/2023, I need to get below data.

Market	AS of 04/22	AS of 04/23	AS of 04/24	AS of 04/25	Total	Number of days
Atl	11	8	7	8	34	4
Chr	11	11	10	10	42	4

Final Output

Market	Total / Number of days
Atl	8.5
Chr	10.5

lbendlin · ‎04-26-2023

Something like this?

You can use averagex() instead of sumx() for the final result.

see attached.

How to achive below looping logic in powerbi

Helpful resources

Fabric Data Days

Power BI Monthly Update - October 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

Fabric Data Days starts November 4th!