Forum Discussion

josegracaaxians's avatar
josegracaaxians
Icon for Helper I rankHelper I
2 years ago
Solved

Forecast with power Bi

Good evening,


Hoping someone can help me figure this out. I've tried many different options but none of them was efficient.


I have a model with 2 tables, the human resource database with the hours, áreas, and types of consultant, and a calendar table.

 

I've done two measures with simple things. "Horas contratadas"

and 

 I have this table:

and what I wanted is like :

And I need to predict the rest of hours based on recent consumption (based on last 3/6 months)

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI josegracaaxians,

    Sure, I think these date range not existed in your fact table, right?
    IF that is the case, I'd like to suggest you change the 'horas realizadas' expression to check if the current calendar date existed in your table.
    Then you can find out the last date which has value as the output for the not existed date ranges.

    horas realizadas =
VAR currDate =
    MAX ( 'Calendar'[Date] )
VAR _lastDate =
    MAXX ( ALLSELECTED ( Eventos ), [Date] )
VAR lastResult =
    CALCULATE (
        SUM ( Eventos[horas] ),
        FILTER ( ALLSELECTED ( Eventos ), [Date] = _lastDate )
    )
RETURN
    IF ( currDate >= _lastDate, lastResult, SUM ( Eventos[horas] ) )

    Regards,

    Xiaoxin Sheng

3 Replies

    • josegracaaxians's avatar
      josegracaaxians
      Icon for Helper I rankHelper I
      2 years ago

      Good morning, 

      'Horas realizas is a simple sum of database :

      Thank You for your attencion.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        HI josegracaaxians,

        Sure, I think these date range not existed in your fact table, right?
        IF that is the case, I'd like to suggest you change the 'horas realizadas' expression to check if the current calendar date existed in your table.
        Then you can find out the last date which has value as the output for the not existed date ranges.

        horas realizadas =
VAR currDate =
    MAX ( 'Calendar'[Date] )
VAR _lastDate =
    MAXX ( ALLSELECTED ( Eventos ), [Date] )
VAR lastResult =
    CALCULATE (
        SUM ( Eventos[horas] ),
        FILTER ( ALLSELECTED ( Eventos ), [Date] = _lastDate )
    )
RETURN
    IF ( currDate >= _lastDate, lastResult, SUM ( Eventos[horas] ) )

        Regards,

        Xiaoxin Sheng