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
Anonymous
Not applicable

Count of records for specific date range/ refer cells for value

Hi,

 

I am trying to count the records for a specific date range. I have two tables 

 

table 1: has count of records for every category by date


table 1.PNG

 

 the date column has records from the year 2017. to visualise for a particular week, i have applied basic filtering where i  have manually selected these above shown dates. 

 

table 2: has count of category by total and total before this week[previous]
table 2.PNG

 

inorder to display count of previous i want to write a dax that subtracts the count of a particular week from the total count of category. 

 

the problem i am facing is, i dont want to give the dates in the dax, i want to automate where it automatically takes a specific date range to count the records. example, every previous week monday to friday is my default week setting. Please suggest me ways to do the same. 

 

or, i wish to refer to a cell to use that value. Please let me know if this is possible. 

 

Thanks for your help!!

 

 

 

3 REPLIES 3
Anonymous
Not applicable

Mate, your explanation is highly obscure. If you don't give us a VERY CLEAR explanation of what it is you want to do and the model, please do not be surprised if you don't get any answers.

I'd suggest you read this:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Best
Darek
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Please can you provide a data sample and the desired outcome?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

Hi @Mariusz

 

https://1drv.ms/u/s!Ap-4rMiqmKwTgYMY1_fild0kj79ncw?e=AzIilD 

 

I have uploaded a sample file in this link. Please view this. In table 2 of the pbi file, you will find a new measure called count of previous category where i am trying to count all records before a certain week period. 

 

Currently i have used 

count of previous = CALCULATE( COUNT(category[Category]), category[created date] < DATE(2019, 7, 22))

table 2.PNGtable 1.PNG

instead of the above formula, i want to refer to table 1 to get the date's value for every category and subtract it from total. 

 

count of previous category = total count of records - count of records for dates shown in table 1

 

in excel i used this formula : 

=COUNTIFS(' Data'!$C:$C,'Report 1'!$A8)-SUM(C8:I8)

 

where sum(c8:18) used to contain value for categories for various dates. 

 

Please suggest ways to do the same. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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