Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Experts,
Suppose I have below date with some value as well as missing value.
Date Value
8/18/2020---45
8/19/2020---36
8/20/2020---25
8/21/2020
8/22/2020
8/23/2020
8/24/2020---96
8/25/2020---22
8/26/2020---89
8/27/2020
8/28/2020---56
8/29/2020---85
Here we have missing data for date 8/27/2020 (1 day )and from date 08/21 to 08/23 (3 days)
I want to create a measure in such a way that I can get a average data for missing date.
For Example:-(25+96)/2---from date 08/21 to 08/23 and (89+56)/2--for date 8/27
Thanks,
Annu
Solved! Go to Solution.
Hi, @Annu_choubey , pls refer to a DAX solution.
Avg =
VAR __prev_nonblank =
LASTNONBLANKVALUE (
FILTER ( ALL ( DS[Date] ), DS[Date] <= MIN ( DS[Date] ) ),
MAX ( DS[Value] )
)
VAR __next_nonblank =
FIRSTNONBLANKVALUE (
FILTER ( ALL ( DS[Date] ), DS[Date] >= MIN ( DS[Date] ) ),
MAX ( DS[Value] )
)
RETURN
IF (
ISBLANK ( MIN ( DS[Value] ) ),
( __next_nonblank + __prev_nonblank ) / 2,
MIN ( DS[Value] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi, @Annu_choubey , pls refer to a DAX solution.
Avg =
VAR __prev_nonblank =
LASTNONBLANKVALUE (
FILTER ( ALL ( DS[Date] ), DS[Date] <= MIN ( DS[Date] ) ),
MAX ( DS[Value] )
)
VAR __next_nonblank =
FIRSTNONBLANKVALUE (
FILTER ( ALL ( DS[Date] ), DS[Date] >= MIN ( DS[Date] ) ),
MAX ( DS[Value] )
)
RETURN
IF (
ISBLANK ( MIN ( DS[Value] ) ),
( __next_nonblank + __prev_nonblank ) / 2,
MIN ( DS[Value] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank You😊!!
You can try by going into Power Query
- Duplicate your Value column
- Fill Orignal Value column by Up
- Fill Duplicated Value column by Down
- Add a custom Column (New Value) by ([Value] +[Value - Copy])/2
- This new column will have your desired results.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |