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 a...
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.
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] )
