Problem between two dates

[dorsey]

Hi,

My project going well, but, like a newbee, I have a lot of question. First thank you for helping me from the beginning. 

My new question is about the date. More specifically about the time between two dates. 

To explain here is my table : 

This represente the holiday demand of the user.

The column "delay" count automatcally the "working" day between the column "start" and "end" with this : 

Delay = IF('altissim_gt altissim_gt_holiday'[APM_DAY]="day",var noworkdays = IF ('altissim_gt altissim_gt_holiday'[CONTRAT]= "MC", CALCULATE(COUNT('Calcul_ferié'[Date]),FILTER('Calcul_ferié','altissim_gt altissim_gt_holiday'[start]<='Calcul_ferié'[Date]&&'altissim_gt altissim_gt_holiday'[end]>='Calcul_ferié'[Date]&&('Calcul_ferié'[isholiday_MC]=1||'Calcul_ferié'[isweekend]=1))),CALCULATE(COUNT('Calcul_ferié'[Date]),FILTER('Calcul_ferié','altissim_gt altissim_gt_holiday'[start]<='Calcul_ferié'[Date]&&'altissim_gt altissim_gt_holiday'[end]>='Calcul_ferié'[Date]&&('Calcul_ferié'[isholiday]=1||'Calcul_ferié'[isweekend]=1)))) return DATEDIFF('altissim_gt altissim_gt_holiday'[start],'altissim_gt altissim_gt_holiday'[end],DAY)-noworkdays+1,0.5)

My problem is when I use the "start" filter or the "end" filter, it remove the line.

For exemple : 

I put this on my filter start : 

and return this : 

What I looking for is the first table but with the "start date" as the filter.

Not sur it's clear but... sorry for my english

I thinks this post Is the start for me, but I think too that my delay working after that.

https://community.powerbi.com/t5/Desktop/Date-between-two-dates/m-p/823159#M395490

Thank you for your help.

[dorsey]

Hi there,

I've try a lot of thing, but I'm still stuck with this problem. I think it's too close but anyway it's notworking. If someone look around I'm very interresting

Kind regards

[v-shex-msft]

HI @dorsey,

Maybe you can take a look at the following link to create a new expanded table about detailed records of the date range, then you can simple operate with that date ranegs:

Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community

Regards,

Xiaoxin Sheng

[dorsey]

Thank you for your answer, It's very close to working and thank you for that.

I've juste a little problem.

ID_USER 64 have taken 3 day on febrary and 1 day on may, but on my visualisation I have accumulation of this day on febrary and on may as you see here : 

Is it possible to have 3 on Febrary and 1 on May ? 

If my .pbix help you can find it here : 

https://altissimalearning.net/Holiday_test2.pbix

thank you again for your help

regards

[v-shex-msft]

Hi @dorsey,

It looks like these records not mapping correctly and they have been aggregated, you can try to use the following measure formula to split these aggregated values:

Measure = 
CALCULATE (
    SUM ( 'altissim_gt altissim_gt_holiday'[DUREE_GT] ),
    FILTER (
        ALLSELECTED ( 'altissim_gt altissim_gt_holiday' ),
        [start] in VALUES( 'Calcul_ferié'[Date] )
    ),
    VALUES ( 'altissim_gt altissim_gt_holiday'[ID _USER] )
)

Regards,
Xiaoxin Sheng

[dorsey]

Thank you Xiaoxin Sheng for your answer.

it's very very close, but stil 1 problem. 

When holiday taken by user is over two month (not sur it's good in english sorry).

For exemple ID_USER 1 have taken is holiday between 07-30-21 to 08-30-2021.

Normaly we should have 1 on july and 19 on august actually we have 21 on july.

To be exact, if we reject non working day, we need to have no day on july and 19 on august.

I Hope I'm clear I've a very bad english.

Also, I have change the code by the following because the good column is "delay"

Measure = 
CALCULATE (
    SUM ( 'altissim_gt altissim_gt_holiday'[Delay] ),
    FILTER (
        ALLSELECTED ( 'altissim_gt altissim_gt_holiday' ),
        [start] in VALUES( 'Calcul_ferié'[Date] )
    ),
    VALUES ( 'altissim_gt altissim_gt_holiday'[ID _USER] )
)

Again thank you, I learn a lots of things.

Regards

[v-shex-msft]

Hi @dorsey,

Did you mean to exclude the holiday range from this expression calculation? IF that is the case, you can extract the holiday ranges and do except operating in the formula:

Measure =
VAR holidayList =
    CALCULATETABLE (
        VALUES ( holiday[Date] ),
        ALLSELECTED ( holiday ),
        VALUES ( holiday[ID _USER] )
    )
RETURN
    CALCULATE (
        SUM ( 'altissim_gt altissim_gt_holiday'[Delay] ),
        FILTER (
            ALLSELECTED ( 'altissim_gt altissim_gt_holiday' ),
            [start] IN EXCEPT ( VALUES ( 'Calcul_ferié'[Date] ), holidayList )
        ),
        VALUES ( 'altissim_gt altissim_gt_holiday'[ID _USER] )
    )

Regards,

Xiaoxin Sheng

[dorsey]

Hello and thank you for reply.

No the probleme is not here, sorry for my english it's hard to explain.

Myabe with a picture.

Look for this user, it take his holiday between july and august, but in the second tab, the day of holiday (8) was all in july. Maybe it's possible to have 7 in july and 1 on august 

thank you again.

regards