Calculate duration global and by year

[jochendecraene]

Hi

I'm calculating the duration of stay of are customers. 

I used a datediff function that gives me the total duration, wich is fine. But I also need the duration of stay by year, for those customers who's stay exceeds a calenderyear.

so for for 567216 I need the duration in the years 2021, 2022 en 2023; for 637645 for the years 2021 en 2022.

Can someone help? Thnx!

[Anonymous]

Hi @jochendecraene,

Do you mean you want to get the years that include in the data ranges?

If that is the case, you can use them as variable with calendar function to create calendar with all date records and the custom field year, then you can use concatenatex function to merge them.

formula =
CONCATENATEX (
    DISTINCT (
        SELECTCOLUMNS (
            CALENDAR ( [StartDate], IF ( [Enddate] <> BLANK (), [EndDate], TODAY () ) ),
            "Year", YEAR ( [Date] )
        )
    ),
    [Year],
    ","
)

Regards,

Xiaoxin Sheng

[jochendecraene]

Maybe my question is somewhat confusion.

I'll try to explain in a better way:

So I got this table with customers and an end- and startdate of there stay with us. I use a datediff function to get the global duration of their stay.

Now, I also need the days they stayed by year. That is easy when their stay doesn't exceed 1 calenderyear, but it gives me trouble when a customers stay exceeds 1 calenderyear.

This table in the picture below is the result I need.

I hope I explained it better this way.

[Anonymous]

Hi @jochendecraene,

You can use the following measure formula to instead the 'days in year' it will help you to filter the years that not in the range:

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR list =
    DISTINCT (
        SELECTCOLUMNS (
            CALENDAR ( [StartDate], IF ( [Enddate] <> BLANK (), [EndDate], TODAY () ) ),
            "Year", YEAR ( [Date] )
        )
    )
RETURN
    IF (
        YEAR ( currDate ) IN list,
        DATEDIFF ( [StartDate], IF ( [Enddate] <> BLANK (), [EndDate], TODAY () ), DAY )
    )

Regards,

Xiaoxin Sheng

[jochendecraene]

I can't seem to resolve this scenario.

So I got this to work with:

When I use the measure as a calculated colums I get this result

The solution I'm looking for is this

Maybe this is not possible in power bi, I don't know ... I guess I can work wiht separate datasets by year and do it that way. I than have to calculate every set separatly. But that's a solution, be it more time consuming.

[jochendecraene]

@Anonymous  I tried adding a calender and linked it to startdate or enddate, but I keep getting an error in de currDate

[jochendecraene]

Thnx,

One last question: where do i get the currDate?

VAR currDate =

    MAX ( Table[Date] )

I get an error on the ( Table[Date] )

[jochendecraene]

Hi @Anonymous 

Thnx for your help. 

This is almost what I need. With your forumal I get the numbers of the years of the period a customer stayed with us.

This is very good, but I also need the number of days per year, like in the picture below

Thanks for your help!