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.
I am struggling to create a measure that sums the product of the EOM cost * EOM QOH in the table (attached below) including end of month dates that are not present. I have multiple part numbers in a list with all end-of-month dates where transactions occurred. For months where no transactions occurred, we still have a quantity on hand (QOH) and cost from the previous month that needs to be included in the measure. For example, in the table below, the measure for part number A should include the months where the data is missing, in other words for part A, the measure should equal:
8*84 (jan 2018) + 7*84 (Feb 2018) + 7*84 (no value for March 2018 so use prev non zero value from Feb) + 7*84 (no value for April 2018 so use previous non-zero value from Feb) + 7*84 (no value for May 2018 so use previous non zero value for Feb) + 6*84 (jun 2018) + 5*84 (jul 2018) + 5*84 (no value for Aug 2018 so use previous non zero value for Jul) + 4*84 (sept 2018) + ...... etc...
I want to do this for each part number.. so part number B would have the same type of calculation associated with it.
Any help would be MUCH appreciated,
DataTable
End of month | Part Number | EOM QOH | EOM Cost |
1/31/2018 | A | 8 | $84 |
2/28/2018 | A | 7 | $84 |
6/30/2018 | A | 6 | $84 |
7/31/2018 | A | 5 | $84 |
9/30/2018 | A | 4 | $84 |
3/31/2019 | A | 8 | $84 |
4/30/2019 | A | 7 | $84 |
7/31/2019 | A | 4 | $84 |
9/30/2019 | A | 3 | $84 |
11/30/2019 | A | 2 | $90 |
3/31/2018 | B | 22 | $68 |
4/30/2018 | B | 20 | $68 |
5/31/2018 | B | 17 | $68 |
6/30/2018 | B | 15 | $68 |
7/31/2018 | B | 13 | $68 |
9/30/2018 | B | 10 | $68 |
11/30/2018 | B | 7 | $68 |
2/28/2019 | B | 16 | $68 |
3/31/2019 | B | 15 | $68 |
6/30/2019 | B | 21 | $68 |
7/31/2019 | B | 19 | $68 |
8/31/2019 | B | 16 | $68 |
9/30/2019 | B | 12 | $68 |
12/31/2019 | B | 22 | $70 |
Solved! Go to Solution.
Hi @CL7777 ,
You can create a measure as below:
Measure =
VAR _curdate =
MAX ( 'temp table'[Last Day of Month] )
VAR _curpart =
MAX ( 'temp table'[Part Number] )
VAR _predate =
CALCULATE (
MAX ( 'temp table'[Last Day of Month] ),
FILTER (
ALL ( 'temp table' ),
'temp table'[Part Number] = _curpart
&& 'temp table'[Last Day of Month] < _curdate
&& NOT ( ISBLANK ( 'temp table'[QOH] ) )
&& NOT ( ISBLANK ( 'temp table'[Cost] ) )
)
)
VAR _prevalue =
CALCULATE (
MAX ( 'temp table'[QOH] ) * MAX ( 'temp table'[Cost] ),
FILTER (
ALL ( 'temp table' ),
'temp table'[Part Number] = _curpart
&& 'temp table'[Last Day of Month] = _predate
)
)
RETURN
IF (
ISBLANK ( MAX ( 'temp table'[Cost] ) ) && ISBLANK ( MAX ( 'temp table'[QOH] ) ),
_prevalue,
MAX ( 'temp table'[QOH] ) * MAX ( 'temp table'[Cost] )
)
Best Regards
Rena
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you very much, I opened the pbix file but the table created looks correct, but there is no measure in the pbix that computes what I am looking for. Am I missing something?
Thanks @Ashish_Mathur for the sample file.
Try this measure:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Otherwise, to do this with a Measure, you will need to create the table as a visualization:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
I appreciate all of your help, I cannot use power query to make the table because my data set is created in Dax in BI using a summarize statement and is millions of lines. but I am almost there, one last thing I need help with to get this figured out. I created a table in Dax that has all the dates and data in there. it looks like this now (see sample table below)
I tried using your lastnonblank value measure statement to create the total value (QOH * Cost) for the last non-blank row in the table below (table called "temp table"). Im not getting values where the QOH and Cost is blank, in other words, its not retrieving the last non blank value of these and multiplying them together. Here is what Im using for the measure, what am I doing wrong?
Last Day of Month | Part Number | Plant | QOH | Cost |
10/31/2020 | A | MfgSys | ||
9/30/2020 | A | MfgSys | ||
8/31/2020 | A | MfgSys | ||
7/31/2020 | A | MfgSys | ||
6/30/2020 | A | MfgSys | ||
5/31/2020 | A | MfgSys | ||
4/30/2020 | A | MfgSys | ||
3/31/2020 | A | MfgSys | ||
2/29/2020 | A | MfgSys | ||
1/31/2020 | A | MfgSys | ||
12/31/2019 | A | MfgSys | 71 | 4.37 |
11/30/2019 | A | MfgSys | ||
10/31/2019 | A | MfgSys | 74 | 4.43 |
9/30/2019 | A | MfgSys | ||
8/31/2019 | A | MfgSys | ||
7/31/2019 | A | MfgSys | 76 | 4.43 |
6/30/2019 | A | MfgSys | 82 | 4.43 |
5/31/2019 | A | MfgSys | 86 | 4.43 |
4/30/2019 | A | MfgSys | ||
3/31/2019 | A | MfgSys | ||
2/28/2019 | A | MfgSys | 98 | 4.43 |
1/31/2019 | A | MfgSys | ||
10/31/2020 | B | MfgSys | ||
9/30/2020 | B | MfgSys | ||
8/31/2020 | B | MfgSys | 18 | 222.12 |
7/31/2020 | B | MfgSys | 19 | 222.12 |
6/30/2020 | B | MfgSys | ||
5/31/2020 | B | MfgSys | 20 | 222.12 |
4/30/2020 | B | MfgSys | ||
3/31/2020 | B | MfgSys | 14 | 222.12 |
2/29/2020 | B | MfgSys | ||
1/31/2020 | B | MfgSys | 4 | 222.12 |
12/31/2019 | B | MfgSys | ||
11/30/2019 | B | MfgSys | 7 | 242.40 |
10/31/2019 | B | MfgSys | 8 | 242.40 |
9/30/2019 | B | MfgSys | ||
8/31/2019 | B | MfgSys | ||
7/31/2019 | B | MfgSys | 5 | 242.40 |
6/30/2019 | B | MfgSys | 7 | 242.40 |
5/31/2019 | B | MfgSys | 15 | 242.40 |
4/30/2019 | B | MfgSys | 8 | 242.40 |
3/31/2019 | B | MfgSys | ||
2/28/2019 | B | MfgSys | 12 | 242.40 |
1/31/2019 | B | MfgSys | 13 | 242.40 |
Hi @CL7777 ,
You can create a measure as below:
Measure =
VAR _curdate =
MAX ( 'temp table'[Last Day of Month] )
VAR _curpart =
MAX ( 'temp table'[Part Number] )
VAR _predate =
CALCULATE (
MAX ( 'temp table'[Last Day of Month] ),
FILTER (
ALL ( 'temp table' ),
'temp table'[Part Number] = _curpart
&& 'temp table'[Last Day of Month] < _curdate
&& NOT ( ISBLANK ( 'temp table'[QOH] ) )
&& NOT ( ISBLANK ( 'temp table'[Cost] ) )
)
)
VAR _prevalue =
CALCULATE (
MAX ( 'temp table'[QOH] ) * MAX ( 'temp table'[Cost] ),
FILTER (
ALL ( 'temp table' ),
'temp table'[Part Number] = _curpart
&& 'temp table'[Last Day of Month] = _predate
)
)
RETURN
IF (
ISBLANK ( MAX ( 'temp table'[Cost] ) ) && ISBLANK ( MAX ( 'temp table'[QOH] ) ),
_prevalue,
MAX ( 'temp table'[QOH] ) * MAX ( 'temp table'[Cost] )
)
Best Regards
Rena
Thank you so much, that is exactly what I was looking for ! much appreciated
You are welcome Allison.
Hi,
I am not sure of whom you are replying to. In the file download link that i shared, there is defenitely a measure. Please check again.
I really appreciate your response. I do not see a measure that calculates what I need. I see a table called "all months for each part" which has what I need but I do not see how that table was calculated. The only measure I show is for a variable called inventory value which just mulitplies EOM cost times EOM QOH.. The table is exactly what I want, but I do not see the code that created that table.
Hi,
Has @AllisonKennedy answered your question? I have used the Query Editor to transform the data.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Do you have a Date table?
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
That will help give you all dates that are not present and you can calculate the measure over the date table, and use the Date[Month] in the matrix to get the result you want. Then you can use LASTNONBLANKVALUE to help get the result you want.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thanks for your response. I do have a date table but im still unclear how I use the last non blank function to create my desired results. would you be able to provide me a sample measure that I could try?
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |