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 calculated column with datediff function.

RO_PO = DATEDIFF(Provisioning[CREATION_DATE],LASTDATE(Dates_report),DAY)
and then calculating the average Aver_days = AVERAGE(Provisioning[RO_PO])
 
But I want to see a table of Average days on every Monday for past 2 months based on my Dates table with Mondays in it. 
for example.
Dates_report
Monday, December 24, 2018
Monday, December 17, 2018
Monday, December 10, 2018
Monday, December 3, 2018
Monday, November 26, 2018
 
 
 
Need Help

9 Replies

  • 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