Variation IN and OUT

[ricod]

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

[Anonymous]

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

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ricod]

hi, 

it's not ok, i've only one raw with arrival and departure date. so it's not what expected

[Ashish_Mathur]

So based on the picure in my previous post, what result are you expecting?

[ricod]

The calculation is ok but the data in the source files is like this : 

type	arrival	departure
dog	01/01/2025	02/01/2025

cheers

[Ashish_Mathur]

That's exactly how i have treated the source.  In the Query editor, i have unpivoted the data.  Click on Transform data to review the steps applied to your original table.

[ricod]

hi, ok i see. i'll try, but on my sources files i have many others columns 

[ricod]

Hi, it's progressing, i just need to start the calculation of variance from a specific date (i mean, start calculation from 1 septembre 2024, the variance = 0, and after i start calculating arrival and departure)

[ricod]

and the calculation must continue if year change (the calculation seem to be reset changing year)

[Anonymous]

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

[Fowmy]

@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]

[anilelmastasi]

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	4

If that helps, please accept as a solution!

Thank you.

[ricod]

the april result is not correct, should be 2

[anilelmastasi]

Could you write this formula?

Variation = 
VAR CurrentMonth = MAX(DateTable[Date])
RETURN
SUMX(
    FILTER(
        ALL(DateTable),
        DateTable[Date] <= CurrentMonth
    ),
    [IN] - [OUT]
)