Forecast

[jemalosa2000]

Good afternoon forum,

Let's see if someone can help me.
I have a table like this and I want that my Forecast_ticket, for when a month still has no data, fills me with the data of the month of the previous year, ie for 01-02-2024 appears Service with 119 and Customer with 12 any ideas?

It is a much more extensive table, but as an example:

FechaMes Department tickets Forecast_ticket
01/02/23 Service 119 119
01/02/23 Customer 12 12
01/03/23 Service 510 510
01/03/23 Customer 62 62
01/04/23 Service 504 504
01/04/23 Customer 60 60
01/05/23 Service 527 527
01/05/23 Customer 62 62
01/06/23 Service 508 508
01/06/23 Customer 60 60
01/07/23 Service 527 527
01/07/23 Customer 62 62
01/08/23 Service 527 527
01/08/23 Customer 62 62
01/09/23 Service 510 510
01/09/23 Customer 60 60
01/10/23 Service 527 527
01/10/23 Customer 62 62
01/11/23 Service 510 510
01/11/23 Customer 60 60
01/12/23 Service 511 511
01/12/23 Customer 62 62
01/01/24 Service 527 527
01/01/24 Customer 62 62
01/02/24 Service
01/02/24 Customer
01/03/24 Service
01/03/24 Customer
01/04/24 Service
01/04/24 Customer
01/05/24 Service
01/05/24 Customer
01/06/24 Service
01/06/24 Customer
01/07/24 Service
01/07/24 Customer
01/08/24 Service
01/08/24 Customer
01/09/24 Service
01/09/24 Customer
01/10/24 Service
01/10/24 Customer
01/11/24 Service
01/11/24 Customer
01/12/24 Service
01/12/24 Customer

[v-kaiyue-msft · Accepted solution]

Hi @jemalosa2000 ,

Please follow these steps:

1.Create a calculated column to find the tickets value.

new tickets =

IF(

    ISBLANK([tickets]),

    CALCULATE(

        SUM('Table'[tickets]),

        FILTER(ALL('Table'),DATE(YEAR('Table'[FechaMes])+1,MONTH('Table'[FechaMes]),DAY('Table'[FechaMes])) = EARLIER('Table'[FechaMes]) && 'Table'[Department] = EARLIER('Table'[Department]))

    ),

    [tickets]

)

2.Create a calculated column to find the Forecast_ticket value.

new Forecast_ticket =

IF(

    ISBLANK([Forecast_ticket]),

    CALCULATE(

        SUM('Table'[Forecast_ticket]),

        FILTER(ALL('Table'),DATE(YEAR('Table'[FechaMes])+1,MONTH('Table'[FechaMes]),DAY('Table'[FechaMes])) = EARLIER('Table'[FechaMes]) && 'Table'[Department] = EARLIER('Table'[Department]))

    ),

    [Forecast_ticket]

)

3.The final result is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.