Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Average calculation based on dates

Hi 
I need to calculate the average time of these priorities and something is missing in my equation because it should only include the dates if "Afsluttet date" is blank and I know for a fact that all 1. priorities only have an "aflsuttet date"(enddate), so it should not appear in the graph. 
 

 

Average time =
VAR _dato = MAX(Kalender[Date])
RETURN
AVERAGEX(
    CALCULATETABLE(
        Data,
        ALL(Kalender),
        Data[Registreret dato]<_dato && (Data[Afsluttet dato] > _dato || ISBLANK(Data[Afsluttet dato]))
    ),
    DATEDIFF(Data[Registreret dato], _dato, DAY)
)
 
What is wrong with the equation above?
 
Best regards,
Lisa 
Need Help

2 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    Anonymous 

    Average time =
    VAR _dato =
    MAX ( Kalender[Date] )
    RETURN
    AVERAGEX (
    FILTER (
    Data,
    CALCULATE (
    ALL ( Kalender ),
    Data[Registreret dato] < _dato
    && OR ( Data[Afsluttet dato] > _dato, ISBLANK ( Data[Afsluttet dato] ) )
    )
    ),
    DATEDIFF ( Data[Registreret dato], _dato, DAY )
    )

     

    Anonymous If this post helps, please consider accept as solution to help other members find it more quickly and Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Mahesh0016 

      What is the right setup? Because if I copy past your equation it says something is wrong. 

       

      VAR _dato =
      MAX ( Kalender[Date] )
      RETURN
      AVERAGEX(
          FILTER (
          Data,
          CALCULATE(
          ALL (Kalender),
          Data[Registreret dato] < _dato
          && OR ( Data[Afsluttet dato] > _dato, ISBLANK ( Data[Afsluttet dato] ) )
          )
          ),
          DATEDIFF ( Data[Registreret dato], _dato, DAY )
      )