Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Monday, December 24, 2018 |
Monday, December 17, 2018 |
Monday, December 10, 2018 |
Monday, December 3, 2018 |
Monday, November 26, 2018 |
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
Proud to be a Super User!
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
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
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.
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
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
Hi @v-shex-msft,
The summarize function finds the difference only for unique dates and in the end gives the wrong numbers.
Thank you very much!
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |