Forum Discussion

AndySawyer's avatar
AndySawyer
Regular Visitor
8 years ago

Using a portion of a slicer

Hi,

 

I really hope someone can help me with this, as I'm new to DAX queries and I've been searching for the answer for hours with no results.

 

I have a slicer with a Month-Year picker, based off a Date table. I want to create a measure that will have the YTD for the current year selected, and also the prior comparable year, which can be displayed on a Stacked Column Chart in Power BI, with a separated column used in the Legend.

 

I have the following formula that work:

 

Total Revenue LYTD =
TOTALYTD(
SUM('Data'[Total Revenue]),
DATEADD('Date Start'[Date],-1,YEAR), "30/06"
)

 

Total Revenue CYTD =
TOTALYTD(
SUM('Data'[Total Revenue]),
'Date Start'[Date], "30/06"
)

 

But if I pull them both onto the chart, I can't use a third identifier (in this case a dimension named 'Market Segment') to color code the chart.

 

I'm figuring that I need to create a calculated measure that performs a YTD calculation for the Month portion of my slicer, ignoring the Year portion, so if the slicer has 'September 2017' selected, I could drag said measure onto the Value section of the chart, add the Market Segment dimension to the Legend and add the 'Date Start'[Financial Year] dimension to the Axis.

 

But I don't know how to do that! Help...

 

Regards,

 

Andy

3 Replies

  • AndySawyer's avatar
    AndySawyer
    Regular Visitor
    8 years ago

    Possibly worth mentioning, if I edit the interactions and remove the interaction between the slicer and the chart, I can get what I want with the following measure:

     

    CALCULATE(TOTALYTD(
            SUM('Data'[Total Revenue]),
            'Date Start'[Date], "30/06"
        ),'Date Start'[MonthName] = "July")

     

    However, I don't want to have to go in and change the month all the time. I want the month to be linked to the slicer (but I don't want the year linked to the slicer).

    • AndySawyer's avatar
      AndySawyer
      Regular Visitor
      8 years ago

      Thank you Sam,

       

      However I'm trying to ensure that users only need to select one date slicer for one report. If I understand what you're suggesting, there would be two date slicers - one for this vis and one for all of the others - or the date would be filtered within the viz and would not change if the user changed the date slicer.

       

      Andy