Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JemmaD
Resolver II
Resolver II

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 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] ) )
3 REPLIES 3
devesh_gupta
Solution Supplier
Solution Supplier

@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 QTDPrevious MTDNext 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.

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

JemmaD_0-1695810839018.png

 

@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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.