Rolling Sum of area with start and end date - DAX

[HopeThomas456]

Hi community! 

I am working on caculating the active area of a building depending on its start and end month. If the area as a NULL end month then that means the space is permanent. If the space has a start month of Jan and and end date in May then that space is not added in July or the months moving forward. This is one of the DAX formate I am working on and it is not giving the correct output. Is there something I am missing? 

Rolling Area Sum =

CALCULATE(

    SUM('JoinData'[Area]),

    FILTER(

        ALL('Calendar'),

        'Calendar'[Date] >= MIN('JoinData'[Allocation Data.Planned Start Date])

        && (

            'Calendar'[Date] <= MAX('JoinData'[Allocation Data.Planned End Date])

            || ISBLANK(MAX('JoinData'[Allocation Data.Planned End Date]))

        )

    )

)

 

Thank you so much for all of yalls help!! 

[Anonymous · Accepted solution]

Hi @HopeThomas456 ,

Any update on this? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[HopeThomas456]

Thank you for your replay @Jihwan_Kim 

I tried the DAX measure above, but I noticed that it does not count into Area. 

When I did this itdid not increase the rolling sum. Is there another way around this? 

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link.

Thank you.

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Area count measure: =
CALCULATE (
    COUNTROWS ( JoinData ),
    FILTER (
        JoinData,
        JoinData[Allocation Data.Planned Start Date] <= MAX ( 'Calendar'[Date] )
            && OR (
                ISBLANK ( JoinData[Allocation Data.Planned End Date] ),
                JoinData[Allocation Data.Planned End Date] >= MIN ( 'Calendar'[Date] )
            )
    )
)