How to sum up data based on condition

[TriveniN]

Hi 

Need help on below query

Created below measure to get data.

Below is the sample data

propcode	Name[Lease-SFV]	chargepostmonth	chargetotalamt	SecondYearRent	Occupied ratio
atl00021	atl00021-AAA	10/1/2021	235	1000	1
Atl00021	atl00021-BBB	9/1/2021	10	1000	1
atl00045	atl00045-CCC	10/1/2021	250	2000	1
atl00076	atl00076-DDD	10/1/2021	195	3000	1
atl00127	atl00127-EEE	10/1/2021	230	2000	1
atl00127	atl00127-XXX	9/1/2021	255	2000	1

If we choose slicer date as October , I need to sum up chargetotalamt for october month data to   SUM(LEASE_SFV[SecondYearRent])   * [OccupiedRatio_Current]  ) for that perticular property.

CALCULATE(

    --Month

    IF(

        ( DATEDIFF(

        MAXX( LEASE_SFV,LEASE_SFV[Lease_Start_Date__c] ) , _dateM, MONTH ) > 12

        && VALUES(ReportPeriod[Type]) = "Month" ),

        ( SUM(LEASE_SFV[SecondYearRent])   * [OccupiedRatio_Current]  )

        +

                 IF(

                  ( DATEDIFF(

        MAXX( LEASE_SFV,LEASE_SFV[Lease_Start_Date__c] ) , _dateM, MONTH ) > 12

        && VALUES(ReportPeriod[Type]) = "Month" ) &&

        FORMAT(MAX(PREMINUMS_YV[chargepostmonth]),"YYYYMM") = FORMAT(_dateM,"YYYYMM")

        ,

        SUM(PREMINUMS_YV[chargetotalamt]),0)

        ,

IF(

        ( DATEDIFF(

        MAXX( LEASE_SFV,LEASE_SFV[Lease_Start_Date__c] ) , _dateM, MONTH ) <= 12

        && VALUES(ReportPeriod[Type]) = "Month" ),

        ( SUM(LEASE_SFV[Rent_on_Lease_Agreement__c])  * [OccupiedRatio_Current]  )

        + IF(

              ( DATEDIFF(

        MAXX( LEASE_SFV,LEASE_SFV[Lease_Start_Date__c] ) , _dateM, MONTH ) <= 12

        && VALUES(ReportPeriod[Type]) = "Month" )

        && FORMAT(MAX(PREMINUMS_YV[chargepostmonth]),"YYYYMM") = FORMAT(_dateM,"YYYYMM") , SUM(PREMINUMS_YV[chargetotalamt]),0)

But I am getting below result.

propcode	Name[Lease-SFV]	chargepostmonth	chargetotalamt	SecondYearRent	Occupied ratio	Expected Result(chargetotalamt + (SecondYearRent * Occupied ratio)
atl00021	atl00021-AAA	10/1/2021	235	1000	1	1235
atl00045	atl00045-CCC	10/1/2021	250	2000	1	2250
atl00076	atl00076-DDD	10/1/2021	195	3000	1	3195
atl00127	atl00127-EEE	10/1/2021	230	2000	1	2230

[Anonymous]

Hi @TriveniN ,

As checked your formula, it seems involve multiple tables. Is there any relationship created among them? Could you please provide some source data in these tables 'LEASE_SFV', 'PREMINUMS_YV' and 'ReportPeriod' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is _dateM a variable? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards