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
ryan_mayu's avatar
ryan_mayu
Super User
7 years ago

baribir

 

I created three tables for this.

 

Fact with date and amount.

Datetime with all continous days.

Date table which is only shows Monday that you determined.

Measure 2 = 
VAR period= DATESINPERIOD('datetime'[Date],LASTDATE(datetime[Date]),-7,DAY)
return if(ISBLANK(SELECTEDVALUE('date'[date])),BLANK(),CALCULATE(AVERAGE('fact'[amount]),period))

Then the result is what you want.

 

 

Thank you and BR

Ryan

  • baribir's avatar
    baribir
    Helper I
    7 years ago

    ryan_mayu

    Thank you!

    This measure calculates the average for last week, I need to calculate from the start of the year.

     

    The whole table is like this

     

     

    On every date, I need the difference between "creation date" and date in the column and then calculate the average of where the difference is less the 14 and count number of rows greater than 30.

    https://1drv.ms/x/s!AqqsPWFldw7PpVkt6QTc7LxxGXSK

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi baribir,

       

      You can try to use following measures if they suitable for your requirement:

      Less than 14 average = 
VAR summary =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Sample' ),
            [User Laber] IN VALUES ( 'Sample'[User Laber] )
        ),
        [User Laber],
        [Datetime],
        "Diff", DATEDIFF ( [Datetime], TODAY (), DAY )
    )
RETURN
    AVERAGEX ( FILTER ( summary, [Diff] < 14 ), [Diff] )
        + 0

Greater than 30 Count = 
VAR summary =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Sample' ),
            [User Laber] IN VALUES ( 'Sample'[User Laber] )
        ),
        [User Laber],
        [Datetime],
        "Diff", DATEDIFF ( [Datetime], TODAY (), DAY )
    )
RETURN
    COUNTROWS ( FILTER ( summary, [Diff] > 30 ) )
        + 0

       

      Regards,

      Xiaoxin Sheng

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