Forum Discussion

Vitor_sp_bo's avatar
Vitor_sp_bo
Helper I
4 years ago
Solved

count on date

Guys, good morning! All right ? I need help with a formula that counts how many times a product has been on sale, 1 product can be in more than one promotion, but if the promotion date has the same ...
Need Help
  • selimovd's avatar
    selimovd
    4 years ago

    Hey Vitor_sp_bo ,

     

    you can just summarize the columns by product and promotion end date and then count the rows with a COUNTX.
    Like this products with the same end date will only count once. The following measure should do that:

    Amount Promotions =
COUNTX (
    SUMMARIZE (
        myPromotions,
        myPromotions[Product],
        myPromotions[Promotion end date]
    ),
    1
)

     

    And it also shows the result you want:

     

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
selimovd's avatar
selimovd
Most Valuable Professional
4 years ago

Hey Vitor_sp_bo ,

 

you can just summarize the columns by product and promotion end date and then count the rows with a COUNTX.
Like this products with the same end date will only count once. The following measure should do that:

Amount Promotions =
COUNTX (
    SUMMARIZE (
        myPromotions,
        myPromotions[Product],
        myPromotions[Promotion end date]
    ),
    1
)

 

And it also shows the result you want:

 

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Vitor_sp_bo's avatar
Vitor_sp_bo
Helper I
4 years ago

Here it ended up going wrong, I believe it may be related to the fact that my "Product" is in another table, I have a product table linked to the promotion table

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

    Hey Vitor_sp_bo ,

     

    can you give an example how your data looks?

    And when you reply please link my username with an @ symbol. Otherwise I will not be notified that you replied.

     

    Best regards

    Denis

    • Vitor_sp_bo's avatar
      Vitor_sp_bo
      Helper I
      4 years ago

      selimovd Table Product:

       

      Table Promotion:

       

      Relation:

      produxt 1 --- * promotion