Forum Discussion
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?
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/EdDOewRVgntFmoxvsCvhKjQBmUYBB9NzjSr2VPwBJLN70g?e=320JT5
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
5 Replies
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
Rovisonn , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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/EdDOewRVgntFmoxvsCvhKjQBmUYBB9NzjSr2VPwBJLN70g?e=320JT5
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
