Need help in calculating distinct key running count

[Anonymous]

Hi,

I am trying to get distinct key running count based on product Type. where the end date must take from date slider and start date should be static to first day in date table even though user adjust the start date. uploaded pbix & Excel files with sample data & business expecations scenarios in below link:

https://gofile.io/d/0RclQs

Any help is much appreciated !!

Thank you 

[MFelix · Accepted solution]

Hi @Anonymous ,

Believe that your data is not correctly calculated since if you look only at 36528 you have the lines below:

3 of the lines are before January 15 and you have two distinct ProductKEY in your summary you are presenting only 1 so if you can confirm the data on the filtered data would be great.

Regarding your issue believe you would be better serve using a before slicer that way people would not be abble to change the start date but having this has you have you can write your measure in the following way:

Distinct key Running count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Product Key] );
    FILTER (
        ALL ( 'Table'[Invoice Date] );
        'Table'[Invoice Date] <= MAX ( 'Date'[Date] )
    );
    USERELATIONSHIP ( 'Date'[Date]; 'Table'[Invoice Date] )
)

When you making a reference to the entire table has you have previously the context picks up all the line values and all the columns so when you take out some columns from your data you will get 9 for every single row. This has to do with the use of measures and their context.

One further question if you activate the relationship there is no need for the use relationship, and if you use that with the before slicer there isn't a need for a measure you can use the column Product Key directly.

[Anonymous]

yes, its 2 unique product keys. i twisted a logic little bit and worked as i expected.

Thanks you Miguel, you are awesome !!