cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patri0t82
Resolver I
Resolver I

Creating a Month Slicer that includes YTD up until Month Selected

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

patri0t82_0-1649162007341.png

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.

 

1 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

yingyinr_2-1649409929614.png

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
        )
    )

yingyinr_1-1649409908468.png

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

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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

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

yingyinr_2-1649409929614.png

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
        )
    )

yingyinr_1-1649409908468.png

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

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

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors