Calculate ignoring a filter

[largetony1210]

Hi,

You probably get this a lot but I'm new to PowerBI and DAX and having some issues with a CALCULATE formula.  It looks like this:

CALCULATE(COUNT(Data[id]), FILTER(Data, Data[Year] = YEAR([**LastWeekDate]) && Data[Quarter] = (CEILING(MONTH([**LastWeekDate])/3,1)-1) && Data[DayofQuarter] < [**CurrentDayofQtr])))

The formula seems to be accounting for the first two filters, but ignoring the third. completely. 

Any suggestions?

Thanks.

[Hardik]

please share the data sample for the same !

[largetony1210]

Anonymised sample of dataset shown below.

I've double checked my measures and they are fine, and have also tested the filters individually, and the problem seems to be "&& Data[DayofQuarter] < [**CurrentDayofQtr]". For some reason, this will not work in the formula. 

[v-yuezhe-msft]

@largetony1210,

What is the data of [**LastWeekDate] and [**CurrentDayofQtr]? Could you please post complete columns of your table that can be copied and pasted?

Regards,

Lydia

[largetony1210]

 @v-yuezhe-msft these are both measures.

[**LastWeekDate] provides the date of the last day in the last week of my dataset: 

**LastWeekDate = MAX(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),1)+([**LastWeek]-1)*7+1)+7

[**LastWeek] here is a whole number.

[**CurrentDayofQtr] is the day number of the above date in the current quarter:

**CurrentDayofQtr = CALCULATE(MIN(Data[DayofQuarter]), FILTER(Data, Data[Year] = YEAR([**LastWeekDate])), FILTER(Data, Data[Month] = MONTH([**LastWeekDate])), FILTER(Data, Data[DayofMonth] = DAY([**LastWeekDate])))

Hope this helps.

[v-yuezhe-msft]

@largetony1210,

Could you please post all the columns that you have mentioned with sample data for us test? You can paste the data into Excel and share Excel via OneDrive.

Regards,
Lydia