Calculation Group to filter dates

[JemmaD]

Hi experts!

I have a table of data counting meetings by date. 

The report consumers want to filter this table by Previous YTD, Previous QTD, Previous MTD, next YTD, next QTD and next MTD.

I think a calculation group would be the best solution, bringing it in as a slicer. However, my calculations, although they seem to be changing the meeting count, are not filtering the dates. 

My syntax for YTD is this, can someone help me figure out how to actually filter the dates rather than the meeting count?

Previous YTD = CALCULATE (
SELECTEDMEASURE(),
DATEADD ( 'Dates'[Date],-1,YEAR ) )

Next YTD = CALCULATE (
SELECTEDMEASURE(),
DATESYTD ( 'Dates'[Date] ) )

[devesh_gupta]

@JemmaD You can modify your syntax for the calculation group to correctly filter the dates. Here’s an example of how you can modify your syntax for Previous YTD and Next YTD:

Previous YTD = CALCULATE (
    SELECTEDMEASURE(),
    DATESYTD ( 'Dates'[Date] ) - 1 * YEAR()
)

Next YTD = CALCULATE (
    SELECTEDMEASURE(),
    DATESYTD ( 'Dates'[Date] ) + 1 * YEAR()
)

In the modified syntax, DATESYTD is used to filter the dates based on the year-to-date period. By subtracting or adding 1 * YEAR(), you can shift the year-to-date period to the previous or next year.

You can apply a similar modification to your syntax for Previous QTD, Previous MTD, Next QTD, and Next MTD by using DATESQTD and DATESMTD functions respectively.

If you find this insightful, please provide a Kudo and accept this as a solution.

[JemmaD]

@devesh_gupta thanks for replying. I'm getting a syntax error with that measure and I can't figure out where to correct it:

[devesh_gupta]

@JemmaD In that case, the way you were creating the measures earlier was looking fine and it should work as expected but I'm not sure why those measures were not filtering dates for you.

In your 'Dates' table, ensure that there is a hierarchy set up correctly for Year, Quarter, and Month. This is crucial for the functions like DATESYTD, DATESQTD, and DATESMTD to work as expected.

Thanks!