DAX DISTINCTCOUNT on a related table

[estgas]

Hi, I have a TMOV table containing DATE and PRODUCT columns, among other columns, that is related with a TDATE table containing DATE and YEAR_MONTH columns.


I would like to populate an additional column in the TMOV table with DISTINCTCOUNT of YEAR_MONTHS by PRODUCT, i.e. number of months that a product moved.

I tried with =CALCULATE(DISTINCTCOUNT(TDATE[YEAR_MONTH]); ALLEXCEPT(TMOV;TMOV[PRODUCT])) but is returning for all records the number of year_months that I have in the TDATE table (i.e. 36 -> 3 years) and not by specific product...

Adding the YEAR_MONTH column to the TMOV table it would work: =CALCULATE(DISTINCTCOUNT(TMOV[YEAR_MONTH]); ALLEXCEPT(TMOV;TMOV[PRODUCT])) but I would like to avoid this way since I have YEAR_MONTH in TDATE table...

Could you please indicate me what I am doing wrong?
Many thanks in advance, Est Gas.

 

[Matt Allington]

Well my first reaction is to ask why you want this column. The description "distinctcount of yearmonths by product" doesn't make any sense to me as a column in your data table. What is the final use you have in mind for this column? Is what is your end state visualisation in a pivot table?

 

[estgas]

Hi Matt, I am using the no. of months that a product did move during the past 12 months for several purposes:
- It's a good indicator to classify fast/medium/slow movers INDEPENDENTLY FROM THE DIMENSION of the unit i.e. Products that did move 1-3 different months during past 12 months are slow movers for big-medium-small sites...
- As an example also the stock policy can be driven by this indicator.
- Also, products that did move every month are the fast movers in my case and I would need to have them in an additional column (vs. calculated filed) because I use them in many pivots tables as filter -> so that I can limit the products rows to fast movers.
I am also using a similar indicator for Products that did move every week instead of every month.
As I was mentioning, I already populated the 'no. of months with consumption' column but I had to create a second additional column with the Month_Year and I think this is not very lean since my TMOV table has 800K rows vs. the TDATE table that has only approx 1K rows.
I hope this opens up the 'months with consumption' concept, I remain curious to hear you comments - beside the technical DAX solution

 

[Matt Allington]

Ok. It sounds like tmov is your data table, and it contains multiple rows per day, possibly many rows for the same product per day in some cases. Certainly many rows for the same product in any one month - for high turnover lines anyway. So what "values" do you expect to see in this column? Based in the description you have given, it sounds like you want to see the number of months - right? So any number from 1 - 12 indicating the total months from the date of the order back in time by 12 months that a sale of at least 1 occurred - is that right? This is a lot of redundant data, and the data will change regularly. Also the number will change over time, so I can't see how you could use this column in a pivot anyway. Ie if you selected 3 from this column in your pivot, then sometimes a product would qualify, and other times it wouldn't - so sales during the time when it equalled 3 would be included, and sales during the time It doesn't would be excluded. A product could have values of 2 and 3 (for example) off and on during any 12 month period.

The data does compress so it is not all bad, but it just seems "wrong" to me - but maybe that's just me.

if you were satisfied to take the current 12 month view of number of months sales, then I would suggest putting this column in a products table. But it won't react to a pivot table there but instead just give a static view based on the period you specify. What do you think?

Also, you may be interested in this

ABC Classification

 

[estgas]

Many thanks for your feedback Matt, you are absolutely right, it would make more sense to have the 'months with consumption' column in the product table, that is smaller then the movements table.

But then, how to do it? I tried with the formula =CALCULATE(DISTINCTCOUNT(TDATE[YEAR_MONTH]); ALLEXCEPT(TMOV;TMOV[PRODUCT])) put in an additional column of the TPROD table and I get '12' for all the records when instead it should be blank to 12 the values...

I am familiar with the ABC classification and I use 'months with consumption' to classify fast/medium/slow movers -> XYZ. ABC and XYZ are the king and the queen of the classification game, ABC based on consumption value and XYZ on consumption frequency: each of the ten AX BX CX AY BY CY AZ BZ CZ blank has it's own character...

 

[Matt Allington]

Well I don't know your table names and structure, but I would think something like this.

=calculate(distinctcount(tmov[year_month]), filter(all(tdate), calculate(tdate[date] >=today()-364 && tdate[date] <= today())))

now I have to say I am not 100% confident it will work as is and normally I would write it and test it to see it works, but without a sample model that is not possible.