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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshua1990
Post Prodigy
Post Prodigy

Slicer for MTD and YTD

Hello everyone!

I have a yearly execution view where I would like to switch between the YTD and MTD.

I have the following to measures:

Orders MTD

 =
CALCULATE (
[Orders],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Fiscal Year]
= MAX ( 'Calendar'[Year] )
&& 'Calendar'[Date]
<= MAX ( 'Calendar'[Date] )
&& 'Calendar'[Fiscal Month Number]
= MAX ( 'Calendar'[Fiscal Month Number] )
)
)

 

 

Orders YTD = 
CALCULATE (
    [Orders],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Fiscal Year]
            = MAX ( 'Calendar'[Year] )
            && 'Calendar'[Date]
                <= MAX ( 'Calendar'[Date] )
    )
)

 

 

How can I create a Slicer for switching between MTD and YTD?

 

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

Create a new table using enter data option and then list the measures names. Then create a slicer out of the column and use this DAX.

SelectedMeasure = 
VAR MeasureName =
    VALUES ( Joshua[Measure Name] )
VAR Result =
    IF (
        HASONEVALUE ( Joshua[Measure Name] ),
        SWITCH ( TRUE (), MeasureName = "Total Sales", [Total Sales], [Total Cost] )
    )
RETURN
    Result

Joshua 1.PNGJoshua 2.PNG

View solution in original post

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

Hi @joshua1990 ,

 

Sorry for that in Power BI, we cannot create a slicer using Measure.

If you want to control measure through slicer, you need to create a new table that contains the Measure’s name like @AntrikshSharma  said.

 

slicer1.jpg

 

slicer2.jpg

 

Maybe you can refer this solved post.

If you have any question, please kindly ask here and we will try to resolve it.

 

Best regards,

 

Community Support Team _ zhenbw

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

AntrikshSharma
Community Champion
Community Champion

Create a new table using enter data option and then list the measures names. Then create a slicer out of the column and use this DAX.

SelectedMeasure = 
VAR MeasureName =
    VALUES ( Joshua[Measure Name] )
VAR Result =
    IF (
        HASONEVALUE ( Joshua[Measure Name] ),
        SWITCH ( TRUE (), MeasureName = "Total Sales", [Total Sales], [Total Cost] )
    )
RETURN
    Result

Joshua 1.PNGJoshua 2.PNG

Thanks for your reply!

Is there maybe another way?

With your approach, I have to edit several measures.

 

 

One another way is through Calculation groups but that is only available for PBI premium and SSAS. I would like to know if anyone else knows any other method.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.