Forum Discussion

baribir's avatar
baribir
Helper I
7 years ago

Create a measure with several date differences

Hi All,   I need your help to solve this issue.   I have to find KPI based on average days a person is making a job. For one date everything is working fine (ex. for today()). I am creating a cal...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi baribir,

 

I do some additional operations in query editor to transform you tables(unpiovt columns and extract week number from unpivoted column), then I create relationship to sheet2 based on week column.

 

 

After above steps, I code measure formulas to calculate average and count with your conditions.

AVERAGE < 14 =
VAR summay =
    SUMMARIZE (
        Sheet1,
        [CREATION_DATE],
        Sheet2[date],
        "Diff", DATEDIFF ( [CREATION_DATE], [date], DAY )
    )
VAR currDate =
    SELECTEDVALUE ( Sheet2[date] )
RETURN
    AVERAGEX ( FILTER ( summay, [Diff] < 14 && [date] = currDate ), [Diff] )


COUNT > 30 =
VAR summay =
    SUMMARIZE (
        Sheet1,
        [CREATION_DATE],
        Sheet2[date],
        "Diff", DATEDIFF ( [CREATION_DATE], [date], DAY )
    )
VAR currDate =
    SELECTEDVALUE ( Sheet2[date] )
RETURN
    COUNTROWS ( FILTER ( summay, [Diff] > 30 && [date] = currDate ) )

 

Regards,

Xiaoxin Sheng

  • baribir's avatar
    baribir
    Helper I
    7 years ago

    Thank you very much!

  • baribir's avatar
    baribir
    Helper I
    7 years ago

    Hi Anonymous,

     

    The summarize function finds the difference only for unique dates and in the end gives the wrong numbers.