topic Re: Count of records for specific date range/ refer cells for value in DAX Commands and Tips

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

Anonymous — Mon, 05 Aug 2019 21:02:41 GMT

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

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

Mariusz — Tue, 06 Aug 2019 07:42:30 GMT

Hi Anonymous

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

Best Regards,
Mariusz

Please feel free to connect with me.

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

Anonymous — Tue, 06 Aug 2019 11:01:50 GMT

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

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

Anonymous — Tue, 06 Aug 2019 14:21:23 GMT

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.