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
baribir's avatar
baribir
Helper I
7 years ago

Hi Anonymous!

 

May be I have mislead you due to my file. I have updated it and shown the calculations what I need. The data there is a sample af all I have. This will be the direct query to sql server.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi baribir,

 

Can you please share a pbix file with some sample data for test?(you can create some fake records with same table structure in excel, load them to sample pbix file) 

 

Regards,

Xiaoxin Sheng

    • 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!