YTD/MTD/QTD slicer for a Direct Lake semantic model

[irynad]

Hi All,

I've been trying to create a report slicer with the following options - YTD, QTD, MTD - allowing the user to click a button and filter out some visuals accordingly. I have a separate date table in my data model.

I normally use a calculation table for this and put one of the fields on a slicer, but it looks like calculated tables are not supported for semantic models in the Direct Lake mode.

I can't find field parameters to try to work around it either.

Is there a way to create a slicer that would function as expected when connected to a Direct Lake semantic model, considering the limitation of not being able to use calculated tables?

Any help is much appreciated.

[v-jialongy-msft]

Hi @irynad 

When working with DirectQuery for Power BI, including Direct Lake semantic models, some features like calculated tables are indeed limited. However, there are alternative approaches you can consider to achieve similar functionality without relying on calculated tables.

Here are some strategies to create time-based slicers in environments where calculated tables are not available:
You can create DAX measures to calculate YTD, QTD, and MTD directly. This method involves using your existing date table and creating measures that dynamically calculate based on the selection in a simpler slicer. For example:

YTD Measure

YTD Sales = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

QTD Measure

QTD Sales = TOTALQTD(SUM(Sales[Amount]), 'Date'[Date])

MTD Measure

MTD Sales = TOTALMTD(SUM(Sales[Amount]), 'Date'[Date])

You would then need to create a simple table manually in your database or a manual entry dataset that contains the labels "YTD", "QTD", "MTD". This can be used as a slicer to toggle the views.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.