cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Count valid items on date between startdate and enddate

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?

 

PBI question.JPG

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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)

 

Capture1.PNG

>>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 )

 

 

Capture2.PNG

 

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

 

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

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)

 

Capture1.PNG

>>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 )

 

 

Capture2.PNG

 

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

 

View solution in original post

This worked like a charm! Thanks a lot!

Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper III
Helper III

@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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors