Forum Discussion
RobRayborn
Helper IV
Helper IVIncorrect totals when using LASTNONBLANK
I have three locations that provide me with their forecast for materials. These three locations use some of the same materials.
Each Month I get a spreadsheet from each location with their material requirements, monthly over the next 6-8 months.
None of the three locations send their requirments on the same day, therefore the 'Date Created' is different for each spreadsheet.
I want to keep track of previously presented requirments so I can track changes. My identifier is the 'Date Created'.
I've created a measure for each location,
Last Date FC "Location1" = CALCULATE([COMBINDED LOCATION QTY], LASTNONBLANK('COMBINED LOCATION QTY'[Date Created], [COMBINED LOCATION QTY]), LOCATION[LOCATION]= "Location1"). I do this for Loction1, Location2, and Location3.
When I then try to get the total by month for each part by using,
Total Locations = [Last Date FC "Location1"]+[Last Date FC "Location2"]+[Last Date FC "Location3"] and apply this measure to a Matrix with Month in the columns and material ID in the rows my total comes out with which ever location had the oldest 'Created Date'.
I need the obvious total, Location1+Location2+Location3, ->for January, for each material id listed.
Total Locations = [Last Date FC "Location1"]+[Last Date FC "Location2"]+[Last Date FC "Location3"] and apply this measure to a Matrix with Month in the columns and material ID in the rows my total comes out with which ever location had the oldest 'Created Date'.
I need the obvious total, Location1+Location2+Location3, ->for January, for each material id listed.
1 Reply
- gmsamborn
Super User
Hi RobRayborn
Have you read either of these?
https://www.sqlbi.com/articles/semi-additive-measures-in-dax/
https://www.daxpatterns.com/semi-additive-calculations/
