Creating a Month Slicer that includes YTD up until Month Selected

[patri0t82]

Hello,

I have a calendar table with many columns, including MonthNumber (1-12) and MonthName (Jan, Feb, etc)

Currently if I want to display year to date values, I have users change the month number on a "between" slicer.

I've hidden the 1 in the left box and leave room for them to enter a month number in the right. IE, 1 - 4

I'd like to create a slicer that will let me choose from the list of month names, but when a month is selected, all moths prior will be included.

[amitchandak]

@patri0t82 , You can use ytd measure to get that.

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

another is you can use before measure with Month Number

Or use an independent date table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[patri0t82]

Thanks for the response. In your solution you suggest using it in a CALCULATE to SUM Sales, which is fine, however I'm planning on using this date selector for many purposes and it would be nice to not have to create a measure for every purpose.  To be clear, I'd just like to create a drop down selection of Months that I could choose from. Except, when I choose March, I'm choosing from Jan1 to Mar31, instead of Mar1 to Mar31. I have a separate year slicer.

Does that make sense?

Thanks again, as always

[v-yiruan-msft · Accepted solution]

Hi @patri0t82 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. 

1. Create a monthno dimension table using Enter Data

2. Create a measure as below to get the sales

NSales = 
VAR _selyear =
    SELECTEDVALUE ( 'Table'[Date].[Year] )
VAR _selmonth =
    SELECTEDVALUE ( 'Months'[Monthno] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            'Table',
            'Table'[Date].[Year] = _selyear
                && MONTH ( 'Table'[Date] ) <= _selmonth
        )
    )

If the above one can't help you get the desired result, please provide some sample data in the tables(exclude sentive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[patri0t82]

I'm sorry I didn't get around to responding earlier! Thank you so much for this, your help is really appreciated.