Calculating the number of products types that are sold in all month during a period (e.g. 6 month)

[pade]

Best Community: I have a newbe problem. I hope the solution would help me understand the DAX Iteration and Filter concept better

The goal is to calculate how many products types that has been sold. I have a 'TransactionTable' with [Dates] and [SoldProducts]Quite easy, I only need to do a DistinctCount on [SoldProducts].

But I shall only calculate the products that has been sold in every month, if the product was not sold in Mars-2016, the product shall not be listed at all (within the period specified below...) 

To make it a bit harder, the calculation shall be done during an observation window of e.g. 6 months back in time. For this I have been figuring out I will use DATESINPERIOD to limit the transaction table: DATESINPERIOD('TransactionTable'[Date];LASTDATE('TransactionTable'[Date]);-6;MONTH)

The outcome shall be able to be sliced in date periods to be able to show in a pivot table of bar graph over time

Any suggestions how to put things togheter

[v-jiascu-msft]

Hi @pade,

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @pade

We could create a “Date” Table first. It’s a good practice to use date intelligence functions with a “Date” table. Then, there are two measure can give us the result.

Measure = DISTINCTCOUNT(TransactionTable[Product])

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( TransactionTable[Product] ),
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -6, MONTH )
)

You can try it with this PBIX file : https://1drv.ms/u/s!ArTqPk2pu-Bkbo6_-RUlMADiu4E

Best Regards!
Dale

[pade]

Thanks @v-jiascu-msft.

But this formula only gives me the number of distinct ProductTypes during a period of 6 month. What I need is a count of all ProductTypes that has been sold within every 6 month in the period. I.E. It must have been sold in both Sep-16, Oct-16, Nov-16, Dec-16, Jan-17, Feb-17 to be able to show up in the Feb-17 statistics

I understand my description might have been a bit unclear on that

And also thanks for creating a very suitable example-file

[richbenmintz]

Hi @pade,

Sounds to me like you would need to count the number of rows in a "product-Month" table and if = 6, then you would count the product as sold. Are you able to share your PBIX file as it is always easier for me if I can create the calcs with the correct data set.

Thanks,

[pade]

Dales file https://1drv.ms/u/s!ArTqPk2pu-Bkbo6_-RUlMADiu4E works perfectly as a base for this @richbenmintz. But since I have more than one ProductType, I can't just calculate the months, not just like that.

The calculations of the number of months must then be per product type, and this is where I'm lost.

 BR //Pär 

[richbenmintz]

Hi @pade

You will find an updated pbix file in the following location with what i believe provides a solution to your issue:

https://1drv.ms/u/s!AhCeuF2piSWMgcAPqQ78uoQFqBKGkw

solution highlights:

- create a simple row count measure

- create a measure that counts the rows of a grouped table including Month and Product (would be product type in your case) where the date range includes the trailing 6 months

- create a measure that tests for the presence of 6 in the previous measure and set the value to 1 where true

/*Simple row count measure*/
rowcount = countrows('TransactionTable')

/*Count the number of rows in the summarized table only including trailing size months by product*/
Count of 6 month window = CALCULATE(COUNTROWS(CALCULATETABLE(SUMMARIZE('TransactionTable', TransactionTable[Product], 'Date'[Date].[MonthNo], "count", [rowcount])) ), DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-6,MONTH))

/*check for existence of 6 rows*/
Has 6 Months Trailing Sales = if(isblank([Count of 6 month window]) = false, if([Count of 6 month window] = 6, 1, 0), BLANK())

screen shot of results

[richbenmintz]

Hi @pade,

I will have a look at your file later today, work getting in the way