Dynamic DAX (SUM) Measure

[Solvera]

I have a scenario where I need to check per month in three month intervals if a prodcut was sold, if it was I then need to sum the occurrences and not the units that were sold. Let me illustrate below:

First I need to check in three month intervals for each month if the product was sold:

If it was, I then need to sum the occurences:

I have a Date Table I use to change the reporting window, so this needs to be a dynamic calculation which I assume DAX is best at.

I have gone as far as checking units sold in the last three months with the below DAX measure but not able to sum the occurrences:

3M Check =

CALCULATE(SUM(Sales[Units]),DATESINPERIOD(DateTable[Date],MAX(Sales[Date]),-3,MONTH))

Any help will be appreciated.

[Anonymous · Accepted solution]

Hi @Solvera

You can create two measures

3M Check 2 = IF([3M Check]>0,1)

Occurrences = SUMX(VALUES(Products[Product]),[3M Check 2])

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Solvera]

Thanks so much! Is it possible to build these two measures (3M Check 2 & Occurrences) into one?

Apologies for not mentioning in the first post, but the data in my model does not have the dates in each column but in one column, will this affect how the measure is cacluated, see below:

[Anonymous]

Hi @Solvera

You can try this measure. 

Occurrences = SUMX(VALUES(Products[Product]),IF([3M Check]>0,1))

Don't worry, my sample data has the same structure as yours so the measure should work. I just added the Month column to Column of a matrix visual for displaying so they appear on multiple columns. 

[Solvera]

Thank you.
I noted you have a Products table with a Product column that is seperate from the Sales table. Please explain the need for that table in this scenario, much appreciated.

[Anonymous]

That's a dimension table. A dimension table usually contains all distinct values. Sometimes in a fact table, if a product is not sold, it displays 0 or null. However sometimes a fact table doesn't have a record for a product that is not sold. This is usually decided by how people collect the data. In the second scenario when a product doesn't have any record in a fact table, if we use Product column from fact table in some visuals, it will miss that Product. I'm not sure if your fact table has records for non-sold products in every month, so I add a dimension table to avoid missing any one. 

Here is a blog for you if you have interest: Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table - RAD...

[Solvera]

Thank you, that makes sense, appreciate your help with this.