Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
M_SBS_6
Helper V
Helper V

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?

 

1 ACCEPTED SOLUTION
vojtechsima
Super User
Super User

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:

vojtechsima_0-1730913660706.png

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

 

vojtechsima_1-1730913681601.png

fact:

vojtechsima_2-1730913690316.png


Calendar is extra, I recommend having it as well.

View solution in original post

1 REPLY 1
vojtechsima
Super User
Super User

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:

vojtechsima_0-1730913660706.png

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

 

vojtechsima_1-1730913681601.png

fact:

vojtechsima_2-1730913690316.png


Calendar is extra, I recommend having it as well.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.