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 trying to calculate an inventory number based on inconsistent data. We have monthly inventory data being loaded for each multiple countries. Each country loads their data at the beginning of the month, but on different days over a period of a week. The inventory table will hold many months worth of history, but I need to calculate the current inventory number. For some countries this will be the current month's, but for others I need to get last months. I have created the following measure:
CALCULATE( sum(Inventory[Total Stock]), LASTNONBLANK ('Date'[FullDate], CALCULATE(COUNTROWS(Inventory))))
This works if all countries have entered their data for the month. E.g. If I use the measure without a date, it returns the current months inventory number, but it also lets me use it with a date to show how the number changed over time.
However, if I try and use it when only some of the countries have entered their data, it does not work correctly. Take a look at the following:
Looking at #1, you will see that only country TH has entered inventory data for October. This is causing the Grand total to be wrong. It should be the sum of the TH October number, plus the Sept totals for the other 3 countries.
Looking at #2, the total is wring again, because the filter using LASTNONBLANK is restricting the data to October. Same thing for #3.
Does anyone know who to modify my measure to include the last month's inventory for each country when doing the totals?
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 |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |