calculate(countrows )) query

[gmazza76]

Good Afternoon,

I have written a DAX formula in PowerPivot to calculate how many people in the business area have made a sale, but I now need to look at previous months data and am wondering how to add this to the code, but I need to be able to do this each day for the full month (weeks in the month) and am unsure how to do this.

Can I just add another filter to match the date as I currently get an error when I do this

=CALCULATE(COUNTROWS(Data),
filter(Data,Data[Agent]=Departmentl[Agent]),
filter(Data,Data[model]=Department[Model])) - This gives me the figure

But I need to add a specific Date for each column I have. I need to get this to calculate in the format "dd/mm/yy hh:mm:ss" as I need to calulate the date from 8pm to 8am the following day or I can use a separate column with the date in format "dd/mm/yy" but it still fails

=CALCULATE(COUNTROWS(Data),
filter(Data,Data[Agent]=Departmentl[Agent]),
filter(Data,Data[model]=Department[Model]),
filter(Data,Data[date]=value"30/11/20")))

thanks in advance
Gavin

 

[gmazza76]

I have realised I was using the wrong formula, I have used DATE(2020,11,30) and made sure the column the formula looks up is as a date