Count valid items on date between startdate and enddate

[Rovisonn]

Hi everyone, 

I'm in a bit of a pickle. I've been breaking my brain over an issue for a while now with little success. So I'm laying down my issues here with some people far more clever than I am 🙂

I'm working with real estate lease contract data. Basically I have two tables:

1. A contract table with real estate rental contracts. The contract table has contractID's, each contract has a city where the real estate property is in and some various other columns (i have left out) with information about the contracts. On top of that each contract has a validfrom and validto date. 

2. A rent price lines table. The rent of one rental contract can consist of a combination of multiple rent lines. I.e. a flat amount with additional costs for maintenance, utilities, administration and so on. The rent lines also have a validfrom and validto date. When there is a change in a rent price (i.e. the flat amount goes up due to indexation/inflation correction) the existing rent line is closed by giving it a validto date and a new line with the new rent price is created. 

I also have a date/calendar table. 

What I want to do is the following. I would like to select any given date from my date/calendar table. Or just any given date in history possible. For this date I would like to know which contracts from the contrac table were valid on this date. Meaning the validfrom <= selected date and the validto >= the selected date. More specifically I would like to count the valid contracts for each city. 

And I would also like to know what the total rent amount is for each valid contract on that given date. Meaning the sum amount where validfrom <= selected date and validto >= selected date. 

Is this even possible or am I looking for the impossible?

[v-deddai1-msft · Accepted solution]

Hi @Rovisonn , 

>>What I want to do is the following. I would like to select any given date from my date/calendar table. Or just any given date in history possible. For this date I would like to know which contracts from the contrac table were valid on this date. Meaning the validfrom <= selected date and the validto >= the selected date. More specifically I would like to count the valid contracts for each city. 

Please try to create a  measure  and add it to visual level filter:

Measure = IF(HASONEVALUE('Table'[Date]),IF(MAX(contract[Vaildfrom])<=MIN('Table'[Date])&&MAX(contract[Vaildto])>=MAX('Table'[Date]),1,0),1)

>>And I would also like to know what the total rent amount is for each valid contract on that given date. Meaning the sum amount where validfrom <= selected date and validto >= selected date. 

You can use the following measure to calculate total rent amount:

rent amount =
VAR a =
    MAX ( 'Table'[Date] )
VAR b =
    SUMX (
        SUMMARIZE (
            'rent price lines',
            contract[Contract ID],
            'rent price lines'[Rent type],
            'rent price lines'[Validfrom],
            'rent price lines'[Validto],
            'rent price lines'[Amount],
            "rent value",
                IF (
                    a >= 'rent price lines'[Validfrom]
                        && a <= 'rent price lines'[Validto],
                    'rent price lines'[Amount],
                    0
                )
        ),
        [rent value]
    )
RETURN
    IF ( b = 0, "invalid", b )

For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdDOewRVgntFmoxvsC...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[DSuresh21]

Hello, Is the pbix still accessible?I am getting 'This site can’t be reached' error, I just would like to understand Cal date is connected to valid from date or valid to date.

[Rovisonn]

This worked like a charm! Thanks a lot!

[amitchandak]

@Rovisonn , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[rajend12]

@Rovisonn 

Hey,

I am very much glad to help you to find a solution... But I am afraid that there is no particular solution to your request at this point....

😥I will keep you posted if I come across any solution of this particular problem !!!!!!!!!!!

Please do reply if any other help required...

Thanks & cheers,

Deepan