cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

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

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Slicer for MTD and YTD

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


Thank you,
Antriksh Sharma

View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Re: Slicer for MTD and YTD

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


Thank you,
Antriksh Sharma

View solution in original post

Highlighted
Post Partisan
Post Partisan

Re: Slicer for MTD and YTD

Thanks for your reply!

Is there maybe another way?

With your approach, I have to edit several measures.

 

 

Highlighted
Super User I
Super User I

Re: Slicer for MTD and YTD

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.

Thank you,
Antriksh Sharma
Highlighted
Community Support
Community Support

Re: Slicer for MTD and YTD

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.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors