Measure - Is this possible? (Rolling DISTINCTCOUNT over time with non-consecutive Dates)

[Styx]

Hello,

I have a table with the following:

• Item
• Store
• Posting Date
• Units
  • Each entry in the ledger is a movement whether positive or negative

I have a Measure to SUM the total of all records where they are less than the MAX([Posting Date]) so I can display [Total Units] across [Year]-[Month]. However, as the [Posting Date] is non-consecutive, I use a relationship to a full 'Date' table. 

I need to do the same thing as a DISTINCTCOUNT of all [Item] where [Total Units] at that period of time is greater than 0. 

Here's what I don't need:

• Cumulative DISTINCTCOUNT across all periods
• DISTINCTCOUNT only for the specified period

What i DO need:

• DISTINCTCOUNT of all [Item] where [Total Units] at any specified period is greater than 0
  
  • This needs to include carry forward of [Item] from earlier [Posting Date]

Example:

[Item].[123456] has Ledger Entries recorded up to 01/01/2016. The SUM of [Units] for [Item].[123456] up to 01/01/2016 is 2. There are no further Ledger Entries recorded. >>Fast forward a year...When looking at 01/01/2017, I still want to count [Item].[123456] in my DISTINCTCOUNT measure. 

For argument's sake, if there was another ledger entry of -1 (leaving the [Total Units] as 1), I would still want to count it. However, if the ledger entry was -2 (leaving the [Total Units] as 0), I no longer want to count it.

Is there any conceivable way to do this? Having 3 fields to GROUP BY that is time sensitive but not consecutive?

Hopefully this explanation makes sense, thank you for any suggestions

[OwenAuger]

Hi @Styx

I'm sure this can be done.

Before trying to offer a solution, just a few clarifying questions:

1. Just confirming, is the [Total Units] measure already defined as a cumulative sum of Units for all dates up to the maximum date in the filter context?
2. Is it correct that you want your measure to return the DISTINCTCOUNT of Items for which [Total Units] >0 as at the current date (where "current date" is the maximum date in the current filter context)?
3. Do you have any related tables for the Item or Store dimensions, or are they just in one table?

Regards,

Owen

[Styx]

Hi Owen,

My relationships are One-to-Many with the "One" tables being:

• Item
• Store
• dTime

My measure for [Total Units] is:

IF(MIN('Store On Hand'[Posting Date])<=CALCULATE(LASTDATE('Store On Hand'[Posting Date]),FILTER(ALLSELECTED('Store On Hand'),'Store On Hand'[Units]<>BLANK())),CALCULATE(SUM('Store On Hand'[Units]),FILTER(ALLSELECTED('Store On Hand'),'Store On Hand'[Posting Date]<=MAX('Store On Hand'[Posting Date]))))

I have tried referencing this measure as [Total Units]>0 in my [Total Lines] measure, but it takes forever calculating and eventually stops with an "Out of Memory in PBI" message. My Ledger Entries are in excess of 10,000,000 records.

Your statement is correct, though, albeit with a further clarification.

I want a total count of all distinct items where the [Total Units] is greater than 0 at any given time period. Across the months in a year, I am expecting to see a cumulative count for all distinct items without summing that count to the next month's count. It also needs to be back dated to include items that match the criteria. 

So, if it exists in the current period - use that DISTINCTCOUNT if [Total Units] >0, otherwise if the last entry in previous dates had [Total Units] >0 then use that DISTINCTCOUNT and combine with the DISTINCTCOUNT of Items that exist in the current period.

I hope this long-winded reply makes sense, and thank you

[v-shex-msft]

Hi @Styx,

Can you please share some sample data to test?

Regards,

Xiaoxin Sheng