Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
we have the next problem.
Several times a year, not every day or month, we count the products in a warehouse.
This inventory table is connected to a date calendar.
Now we i want to know for each day, month, quarter of a year what the count of the products is.
But if we don't have a inventory in example March we need the last value before.
I tried a lot of examples, but the most asume that there is a frequent inventory. In our situation we get a "zero" and we need the last value
Solved! Go to Solution.
Hello @Anonymous
I am assuming you already have such model in place:
I have added a calculated column in the Calendar Table with this formula:
HasInventoryEntry = NOT( ISEMPTY( RELATEDTABLE( Inventory ) ) )
And then used this measure in the Matrix:
Quantity Balance = VAR mxDte = MAX( 'Calendar'[Date] ) RETURN CALCULATE( SUM( Inventory[Quantity] ), CALCULATETABLE( LASTDATE( 'Calendar'[Date] ), 'Calendar'[HasInventoryEntry] = TRUE(), 'Calendar'[Date] <= mxDte ) )
If the inventory can have different categories, like for instance North and South, then you can do it like this:
Quantity Balance = VAR mxDte = MAX ( 'Calendar'[Date] ) RETURN SUMX( VALUES( Regions[Region] ), CALCULATE ( SUM ( Inventory[Quantity] ), LASTNONBLANK ( CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), 'Calendar'[HasInventoryEntry] = TRUE (), 'Calendar'[Date] <= mxDte ), CALCULATE ( COUNTROWS ( Inventory ) ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous
are you able to post a data sample?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Inventory date | Quantity |
1-1-2018 | 500 |
15-1-2018 | 550 |
1-6-2018 | 400 |
1-8-2018 | 300 |
This should be the result
Period | In stock |
januari | 550 |
februari | 550 |
maart | 550 |
april | 550 |
mei | 550 |
juni | 400 |
juli | 400 |
augustus | 300 |
september | 300 |
oktober | 300 |
november | 300 |
december |
Hello @Anonymous
I am assuming you already have such model in place:
I have added a calculated column in the Calendar Table with this formula:
HasInventoryEntry = NOT( ISEMPTY( RELATEDTABLE( Inventory ) ) )
And then used this measure in the Matrix:
Quantity Balance = VAR mxDte = MAX( 'Calendar'[Date] ) RETURN CALCULATE( SUM( Inventory[Quantity] ), CALCULATETABLE( LASTDATE( 'Calendar'[Date] ), 'Calendar'[HasInventoryEntry] = TRUE(), 'Calendar'[Date] <= mxDte ) )
If the inventory can have different categories, like for instance North and South, then you can do it like this:
Quantity Balance = VAR mxDte = MAX ( 'Calendar'[Date] ) RETURN SUMX( VALUES( Regions[Region] ), CALCULATE ( SUM ( Inventory[Quantity] ), LASTNONBLANK ( CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), 'Calendar'[HasInventoryEntry] = TRUE (), 'Calendar'[Date] <= mxDte ), CALCULATE ( COUNTROWS ( Inventory ) ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello @Anonymous
I am assuming you already have such model in place:
I have added a calculated column in the Calendar Table with this formula:
HasInventoryEntry = NOT( ISEMPTY( RELATEDTABLE( Inventory ) ) )
And then used this measure in the Matrix:
Quantity Balance = VAR mxDte = MAX( 'Calendar'[Date] ) RETURN CALCULATE( SUM( Inventory[Quantity] ), CALCULATETABLE( LASTDATE( 'Calendar'[Date] ), 'Calendar'[HasInventoryEntry] = TRUE(), 'Calendar'[Date] <= mxDte ) )
If the inventory can have different categories, like for instance North and South, then you can do it like this:
Quantity Balance = VAR mxDte = MAX ( 'Calendar'[Date] ) RETURN SUMX( VALUES( Regions[Region] ), CALCULATE ( SUM ( Inventory[Quantity] ), LASTNONBLANK ( CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), 'Calendar'[HasInventoryEntry] = TRUE (), 'Calendar'[Date] <= mxDte ), CALCULATE ( COUNTROWS ( Inventory ) ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |