Forum Discussion

Solvera's avatar
Solvera
Frequent Visitor
2 years ago
Solved

Dynamic DAX (SUM) Measure

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Solvera
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.