Forum Discussion
Variation IN and OUT
Hello
i've a folder with xslx files containing cat and dog names. i want from 1st september 2024 calculate the variation of placements. i've tried many thinks with date table or not but i'm not able to do it specially when i filter with type (dog or cat)
can you help ?
xlsx file(s)
| type | arrival | departure |
| dog | 01/01/2025 | 02/01/2025 |
| cat | 01/01/2025 | 01/02/2025 |
| dog | 02/01/2025 | 02/02/2025 |
| dog | 02/01/2025 | 02/02/2025 |
| dog | 04/01/2025 | |
| dog | 04/01/2025 | |
| dog | 04/01/2025 | |
| dog | 04/01/2025 | |
| dog | 03/01/2024 | 03/02/2025 |
| dog | 03/01/2024 | 03/02/2025 |
output expected (with possibility to filter on type cat, dog, ... ) :
| date | IN | OUT | variation |
| Jan. 2025 | 2 | 1 | 1 |
| feb. 2025 | 2 | 3 | 0 |
| mar. 2025 | 0 | 2 | -2 |
| apr. 2025 | 4 | 0 | 2 |
What i've done but it's not ok
14 Replies
Hello ricod ,
1- Create a DateTable.
DateTable = CALENDAR(MIN(YourData[arrival]), MAX(YourData[departure]))You can add column to DateTable:
Month = FORMAT(DateTable[Date], "mmm yyyy") Year = YEAR(DateTable[Date])2- Create IN measure that counts all arrivals for each month
IN = CALCULATE( COUNTROWS(YourData), YourData[arrival] <= MAX(DateTable[Date]), YourData[arrival] > MIN(DateTable[Date]) )Create OUT measure that counts all departures for each month
OUT = CALCULATE( COUNTROWS(YourData), YourData[departure] <= MAX(DateTable[Date]), YourData[departure] > MIN(DateTable[Date]) )Create Variation measure
Variation = [IN] - [OUT]3- Final output should be like this:
Month IN OUT Variation Jan. 2025 2 1 1 Feb. 2025 2 3 0 Mar. 2025 0 2 -2 Apr. 2025 4 0 4If that helps, please accept as a solution!
Thank you.
the april result is not correct, should be 2
Could you write this formula?
Variation = VAR CurrentMonth = MAX(DateTable[Date]) RETURN SUMX( FILTER( ALL(DateTable), DateTable[Date] <= CurrentMonth ), [IN] - [OUT] )
ricod
Create a dates table and a relationship.
Add these measures:IN = COUNTROWS( FILTER( Table01, Table01[arrival] >= MIN(Dates[Date]) && Table01[arrival] <= MAX(Dates[Date]) ) )OUT = COUNTROWS( FILTER( Table01, Table01[departure] >= MIN(Dates[Date]) && Table01[departure] <= MAX(Dates[Date]) ) )Variance = [IN] - [Out]Hi ricod ,
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
Hi,
PBI file attached.
Hope this helps.
hi,
it's not ok, i've only one raw with arrival and departure date. so it's not what expected
So based on the picure in my previous post, what result are you expecting?
Hi ricod,
For analysis on the multiple date field ranges, you can also take a look at the following link about expand the date range and mapping with records:
Solved: Expand date range with repeated pattern - Microsoft Fabric Community
Regareds,Xiaoxin Sheng
