Forecast

jemalosa2000 — Tue, 20 Feb 2024 19:34:51 GMT

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

Re: Forecast

Anonymous — Wed, 21 Feb 2024 02:50:37 GMT

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.

topic Forecast in DAX Commands and Tips

Forecast

Re: Forecast