Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 , 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
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
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
I'm sorry I didn't get around to responding earlier! Thank you so much for this, your help is really appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |