Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
1 year ago
Solved

Count based on dates

Hi, I need to add some totals to a table based on dates. 

 

I have the following table

 

ID.     Cust.    Date_sent

  1.   Kevin. 20/08/2024
  2.   Barry.  20/08/2024
  3.   Louise 21/08/2024

 

What I need is another column to see how many letters have been sent out within 6 months post and including today from date_sent.

 

The other column fields needed would be resp_date (the 6 month period) and count of resp_ID

 

So my table would look something like

 

ID.     Cust.    Date_sent.    Lettervol

  1.   Kevin.  20/08/2024.     2
  2.   Barry.  20/08/2024.     1
  3.   Louise 21/08/2024.     7

 

Any idea what measure to use please?

 

Need Help
  • vojtechsima's avatar
    vojtechsima
    1 year ago

    Hello, M_SBS_6 ,

    given you want physical column, perhaps you can do it in DAX, even tho I would recommend having this as a measure.

    the dim table:

    lettervol = 
CALCULATE(
    COUNTROWS('fact'),
    DATESINPERIOD('calendar'[Date], dim[Date_sent],-6,MONTH)
)

     

    fact:


    Calendar is extra, I recommend having it as well.

1 Reply

  • vojtechsima's avatar
    vojtechsima
    Super User
    1 year ago

    Hello, M_SBS_6 ,

    given you want physical column, perhaps you can do it in DAX, even tho I would recommend having this as a measure.

    the dim table:

    lettervol = 
CALCULATE(
    COUNTROWS('fact'),
    DATESINPERIOD('calendar'[Date], dim[Date_sent],-6,MONTH)
)

     

    fact:


    Calendar is extra, I recommend having it as well.