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

[Anonymous]

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

 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]

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!!

[Anonymous]

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]

Hi @Anonymous 

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

Best Regards,
Mariusz

Please feel free to connect with me.

[Anonymous]

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))

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.