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 all,
I consider myself an intermediate DAX user but I am struggling with this one.. In the table below, I created a measure to calculate the missing QOH values with the last non blank value that occurred in time. So, for example, for part A and plant FL, the value of this measure for the missing QOH for Feb through Apr of 2020 would be 2 (the last non blank value that occurred in time).
End of Month | part number | plant | QOH |
1/31/2020 | A | FL | 2 |
2/28/2020 | A | FL | |
3/31/2020 | A | FL | |
4/30/2020 | A | FL | |
5/31/2020 | A | FL | 4 |
6/30/2020 | A | FL | 6 |
7/31/2020 | A | FL | |
8/31/2020 | A | FL | |
9/30/2020 | A | FL | |
10/31/2020 | A | FL | |
11/30/2020 | A | FL | |
12/31/2020 | A | FL | 10 |
1/31/2020 | A | CA | 1 |
2/28/2020 | A | CA | 2 |
3/31/2020 | A | CA | |
4/30/2020 | A | CA | |
5/31/2020 | A | CA | 5 |
6/30/2020 | A | CA | |
7/31/2020 | A | CA | |
8/31/2020 | A | CA | 8 |
9/30/2020 | A | CA | |
10/31/2020 | A | CA | |
11/30/2020 | A | CA | |
12/31/2020 | A | CA | 2 |
1/31/2020 | B | FL | 11 |
2/28/2020 | B | FL | |
3/31/2020 | B | FL | |
4/30/2020 | B | FL | |
5/31/2020 | B | FL | 13 |
6/30/2020 | B | FL | |
7/31/2020 | B | FL | |
8/31/2020 | B | FL | |
9/30/2020 | B | FL | 4 |
10/31/2020 | B | FL | 5 |
11/30/2020 | B | FL | |
12/31/2020 | B | FL | 8 |
1/31/2020 | B | CA | 22 |
2/28/2020 | B | CA | |
3/31/2020 | B | CA | 24 |
4/30/2020 | B | CA | 11 |
5/31/2020 | B | CA | |
6/30/2020 | B | CA | |
7/31/2020 | B | CA | |
8/31/2020 | B | CA | 13 |
9/30/2020 | B | CA | |
10/31/2020 | B | CA | |
11/30/2020 | B | CA | 6 |
12/31/2020 | B | CA | 7 |
Here is the measure that I created to do this:
Solved! Go to Solution.
@CL7777 Please try these measures:
QOH With Missing =
VAR _d =
MAX ( 'Table'[End of Month] )
VAR _p =
MAX ( 'Table'[plant] )
VAR _s =
MAX ( 'Table'[part number] )
RETURN
IF (
ISBLANK ( SUM ( 'Table'[QOH] ) ),
CALCULATE (
LASTNONBLANKVALUE ( 'Table'[End of Month], SUM ( 'Table'[QOH] ) ),
REMOVEFILTERS ( 'Table' ),
'Table'[End of Month] < _d,
'Table'[plant] = _p,
'Table'[part number] = _s
),
SUM ( 'Table'[QOH] )
)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Hi DataZoe,
I have a quick followup with this post. My model is running very slow with this code, because I have a measure nested in an iterator. and the file is large (2 million rows). Is there a way to do this without putting a measure into an iterator?
@CL7777 This is a good point! Usually at this point I would see if I could push the problem back towards the source. I'm not sure how to do this in Power Query (M), which is one step back. So I'd go another step back, what is the source of the 2 million rows of data? Is it a SQL database?
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@CL7777 Please try these measures:
QOH With Missing =
VAR _d =
MAX ( 'Table'[End of Month] )
VAR _p =
MAX ( 'Table'[plant] )
VAR _s =
MAX ( 'Table'[part number] )
RETURN
IF (
ISBLANK ( SUM ( 'Table'[QOH] ) ),
CALCULATE (
LASTNONBLANKVALUE ( 'Table'[End of Month], SUM ( 'Table'[QOH] ) ),
REMOVEFILTERS ( 'Table' ),
'Table'[End of Month] < _d,
'Table'[plant] = _p,
'Table'[part number] = _s
),
SUM ( 'Table'[QOH] )
)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Worked like a charm.. thank you SO much
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |