Help with DAX formula in a created table to do MTD/QTD/YTD filter

[Anonymous]

I have used this table in several projects and haven't had any troubles before. But i tried to use it today and it doesn't work anymore. 

MTD/QTD/YTD =

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(Query1[Fecha]), YEAR(Query1[Fecha]) = YEAR(TodayDate))

VAR QuarterStart = CALCULATE(STARTOFQUARTER(Query1[Fecha]), YEAR(Query1[Fecha]) = YEAR(TodayDate), QUARTER(Query1[Fecha]) = QUARTER(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH(Query1[Fecha]), YEAR(Query1[Fecha]) = YEAR(TodayDate), MONTH(TodayDate) = MONTH(TodayDate))

VAR Result =

UNION(

ADDCOLUMNS(

CALENDAR(YearStart, TodayDate),

"Selection", "YTD"

),

ADDCOLUMNS(

CALENDAR(QuarterStart, TodayDate),

"Selection", "QTD"

),

ADDCOLUMNS(

CALENDAR(MonthStart, TodayDate),

"Selection", "MTD"

)

)

RETURN

Result

[v-shex-msft]

Hi @Anonymous,

Did PaulDBrown 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[PaulDBrown]

Try using FILTER and the && in the VAR expressions. For example

VAR QuarterStart = CALCULATE(STARTOFQUARTER(Query1[Fecha]), FILTER(Query1, YEAR(Query1[Fecha]) = YEAR(TodayDate) && QUARTER(Query1[Fecha]) = QUARTER(TodayDate)))