topic Re: Count rows based on Two dates in DAX Commands and Tips

Count rows based on Two dates

Anonymous — Mon, 09 Dec 2019 14:30:14 GMT

Hi Team,

I have a data of Start date and End date and product id. I want to calcuate count of product Id based condition on start date and end date.

Example- I want to calcuated count

select count (*) from tabel

where startdate >= '1-8-2019' and end date <= '31-8-2019'

So want to calculate count for every month Jan to Dec based on Start date and end date.

Could you please give some idea about this.

Thanks

Re: Count rows based on Two dates

kentyler — Tue, 10 Dec 2019 01:46:55 GMT

Take a look at the function CALCULATE() it allows you to add more than one filter expression
VAR row_count = CALCULATE(COUNTROWS(table),startdate >= '1-8-2019' ,end date <= '31-8-2019')
this example assumes that there are not external filters on your table, if there are you might want to include a filter expression using ALL()---> ALL(table)

Re: Count rows based on Two dates

Anonymous — Tue, 10 Dec 2019 06:33:29 GMT

Hi,

Thanks for solution.

But I have to do it for all months. I am having data more than 3 years so i want to visuals year wise start date and end date values in X axis.

Please suggest.

Re: Count rows based on Two dates

Anonymous — Tue, 10 Dec 2019 09:09:21 GMT

Got solution from blow link

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=2532cdc3-2024-447d-bdb1-6b85a5178869&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

Thanks!