cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
baribir Regular Visitor
Regular Visitor

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
 
 
 
9 REPLIES 9
ryan_mayu Established Member
Established Member

Re: Create a measure with several date differences

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

screenshot.JPG

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.

screenshot2.JPG

 

 

Thank you and BR

Ryan

baribir Regular Visitor
Regular Visitor

Re: Create a measure with several date differences

@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

 

KPi.png

 

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

 

 

Community Support Team
Community Support Team

Re: Create a measure with several date differences

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

15.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
baribir Regular Visitor
Regular Visitor

Re: Create a measure with several date differences

Hi @v-shex-msft!

 

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.

Community Support Team
Community Support Team

Re: Create a measure with several date differences

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
baribir Regular Visitor
Regular Visitor

Re: Create a measure with several date differences

Community Support Team
Community Support Team

Re: Create a measure with several date differences

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.

2.PNG

 

3.PNG

 

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 ) )

4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
baribir Regular Visitor
Regular Visitor

Re: Create a measure with several date differences

Thank you very much!

baribir Regular Visitor
Regular Visitor

Re: Create a measure with several date differences

Hi @v-shex-msft,

 

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors