Forum Discussion

JemmaD's avatar
JemmaD
Helper V
2 years ago

Calculation Group to filter dates

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 thin...
Need Help
devesh_gupta's avatar
devesh_gupta
Impactful Individual
2 years ago

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's avatar
    JemmaD
    Helper V
    2 years ago

    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's avatar
      devesh_gupta
      Impactful Individual
      2 years ago

      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!