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
I have a table with Warehouse transactions. Each transaction is one record, having for example the location number and the changed quantity (also linked to an items table). The "tricky" this is that since it records transactions, i do not have the actual quantity but the "change" (either addition or removal).
The report will have on data slicer. What i am trying to achieve is to ignore the lowest part of the slicer, therefore to be able to obtain status on the maximum selected (by the slicer) date.
I created a measure "Warehouse Total Remaining Quantity" which works ok. Now i am trying to calculate the Distinct Items remaining on the warehouse (not stock quantity). Therefore i need to have the DIstinct Items having quantities on the Max selected date (therefore "Warehouse Total Remaining Quantity">0)
Below you can see the measures along with a table. Basically the table should be able to show "Warehouse Distinct Items" = 1, where the Quantity>0
Thanks
George
Solved! Go to Solution.
Hi,
This measure works
Measure2 = COUNTROWS(FILTER(CALCULATETABLE(VALUES(Items[PartNo]),DATESBETWEEN('Date'[Date],MAX('Date'[Date]),MAX('Date'[Date]))),[Warehouse Total Remaining Quantity]>0))
Hi,
Does this measure work?
Measure1 = IF([Warehouse Total Remaining Quantity]>0,1,blank())
Measure2 = SUMX(SUMMARIZE(VALUES(Items[Item]),VALUES(Items[Item],"ABCD",[Measure1]),[ABCD])
Drag Measure2 to the visual.
If this does not help, then share the link from where i can download the PBI file. Clearly show the expected result there.
This measure always returns 1. You can find a sample file from: SampleFile
What i fail to understand is why the "Warehouse Distinct Items" is not working. Since the contained summarization is working fine if you put those to a table, i expected to work.... but i gues its more complex than i thought
Thanks
George
Hi,
What is not working? The Warehouse distinct items measure is returing 156 in the card visual. Where is the problem?
The problem is that i only want the max date of the date slicer to filter the measure. If not i will get the distict items which had transactions that date. What i want to get the distinct items with [Warehouse Total Remaining Quantity]>0 at the last day of the "slicer". Its a transactional system, so i have only transactions and not daily status.
Hi,
This measure works
Measure2 = COUNTROWS(FILTER(CALCULATETABLE(VALUES(Items[PartNo]),DATESBETWEEN('Date'[Date],MAX('Date'[Date]),MAX('Date'[Date]))),[Warehouse Total Remaining Quantity]>0))
Thank you vey much @Ashish_Mathur . Worked like a charm. You save me from banging my head against the wall 🙂
You are welcome.
Either I'm missing something or you should change the slicer so that filters for dates before the indicated date...
I have never done this so I don't feel comfortable giving you code and saying "try this".
That said...here's a post about capturing the values in a date slicer. From there, I would think you need two measures:
There are functions that will calculate OPENINGBALANCEMONTH, QUARTER and YEAR...but it sounds like you want more precision than MTM, QTQ or YOY.
I hope this helps...
Try like
Warehouse Distinct Items =
var tabl = SUMMARIZE( all('Warehouse History'); Vessel[Vessel Name]; Items[Item No];
"Remaining";([Warehouse Total Remaining Quantity]) )
RETURN
CALCULATE( DISTINCTCOUNT(Items[Item No]); FILTER( tabl; [Remaining]>0 ) )
Thanks @amitchandak but unfortunately it takes alot of time, leading to a memory error. Does the use of ALL means that all the other filters will be dropped (eg location name, item type)?
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 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |