Ignore Lowest Part of a Date Range

[gap]

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

Warehouse Total Remaining Quantity =

var LastDay=MAX('Date'[Date])

RETURN

CALCULATE(SUM('Warehouse History'[Quantity Changes]); FILTER( ALL('Date');'Date'[Date]<=LastDay))

Warehouse Distinct Items =

var tabl = SUMMARIZE( 'Warehouse History'; Vessel[Vessel Name]; Items[Item No];

"Remaining";CALCULATE([Warehouse Total Remaining Quantity]) )

RETURN

CALCULATE( DISTINCTCOUNT(Items[Item No]); FILTER( tabl; [Remaining]>0 ) )

Thanks

George

[Ashish_Mathur]

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.

[gap]

Hi @Ashish_Mathur 

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

[Ashish_Mathur]

Hi,

What is not working?  The Warehouse distinct items measure is returing 156 in the card visual.  Where is the problem?

[gap]

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.

[Ashish_Mathur · Accepted 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))

[gap]

Thank you vey much @Ashish_Mathur . Worked like a charm. You save me from banging my head against the wall 🙂

[Ashish_Mathur]

You are welcome.

[littlemojopuppy]

Either I'm missing something or you should change the slicer so that filters for dates before the indicated date...

[gap]

Thanks for your reply but this not an option. We need to be able to show changes occured in a period, therefore the slicer should have the from-to. But at the same time we need to be able to see the items at the max selected date. So, since its a transactional system, we need to calculate for the whole period till the max selected date (therefore ignoring the from date)

[littlemojopuppy]

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:

• one to calculate the balance from the beginning of time up to the earliest date selected in the slicer...that should be something along the lines of FILTER(Date,Date[Date] <= (the lowest selected value)
• a second one to calculate the balance from there...FILTER(Date, Date > (lower selected value) && Date <= (higher selected value)

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...

[amitchandak]

@gap 

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 ) )

[gap]

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)?