Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculate between dates +30

Hello,
I had posted previously and someone was able to help me, however the need has changed and when I try to adapt my code, it doesn't do what I need. I hope you can help me.

I also have a table "Calendrier", that is a calendar table.

There is an inactive relationship between TOP[ticket_creation_date] and CALENDRIER[Date] and an active relationship between TOP[ticket_restoration_date] and CALENDRIER[Date].

What I need to do is the following:
I would like to do the 80th percentile of the delay whose end date is between "ticket_creation_date" and 30 days after this date AND whose start date is in the current month (and whose type is different from 1).
Example: For the month of March, I want all those who have a start date in March AND an end date before the date "ticket_creation_date" +30 days and make the 80th percentile of the column "temps retab ticket".
On the example, I don't want to take the ones in red.
On the example, I don't want to take the ones in red.
I tried the following:

délai_retab_m_ceux_creer_m_80_percentile =
VAR ReferenceDate =
MAX ( 'Calendrier'[Date] )
VAR StartDate =
EOMONTH ( ReferenceDate, -1 ) + 1
VAR END30J =
max(TSO[ticket_creationdate])+30
VAR EndThisMonth =
EOMONTH ( ReferenceDate, 0 )
VAR Result =
CALCULATE (
PERCENTILE.INC ('TSO'[temps rétab ticket (j)],0.8),
REMOVEFILTERS ( 'Calendrier' ),
TREATAS (
DATESBETWEEN ( 'Calendrier'[Date], StartDate, END30J ),
'TSO'[ticket_restorationdate]
),
TREATAS (
DATESBETWEEN ( 'Calendrier'[Date], StartDate, EndThisMonth ),
'TSO'[ticket_creationdate]
),
'TSO'[tip0] <> "1"
)
RETURN
Result

But it doesn't work. it doesn't take the right values, I think it comes from my max line; can you help me?

Thank's

Victoria

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , FIrst you need to have measures like, then use percentile

     

    M1= Calculate(Sum('TSO'[temps rétab ticket (j)]), userelationship( 'Calendrier'[Date], 'TSO'[ticket_creation_date]))


    last 30 days=

    Rolling 30 = CALCULATE([M1],DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello,
      It doesn't work or I don't see how to do it, in your solution I can't say that it's the "restoration_date" that must be between these two dates