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.
Hi All,
Having chart with Month name awith values and slicer with Month Name (January to December).
I need to show chart value from January to May if the slicer selection is May or January to August If the slicer selection is August or any month selection in slicer the chart should show values from January to the selection month in slicer.
Can anyone help me.
Thanks in advance
Regards,
Murali.
Solved! Go to Solution.
Hi @Anonymous
If you want to show values before and on the selected month of the current year,
You could create a new table
monthname list =
FILTER (
ADDCOLUMNS (
CALENDARAUTO (),
"monthno", FORMAT ( [Date], "mm" ),
"monthname", FORMAT ( [Date], "mmmm" )
),
DAY ( [Date] ) = 1
)
Create a measure and add it to visual level filter
Measure =
VAR selected_m =
SELECTEDVALUE ( 'monthname list'[Date] )
RETURN
IF (
MONTH ( MAX ( 'Table'[date] ) ) <= MONTH ( selected_m )
&& YEAR ( MAX ( 'Table'[date] ) ) = YEAR ( TODAY () ),
1,
0
)
If you want to show selected year's data, you could create another table,
year list = FILTER(ADDCOLUMNS(CALENDARAUTO(),"yearname",YEAR([Date])),DAY([Date])=1)
Create another measure
Measure 2 =
VAR selected_m =
SELECTEDVALUE ( 'monthname list'[Date] )
VAR selected_y =
SELECTEDVALUE ( 'year list'[yearname] )
RETURN
IF (
MONTH ( MAX ( 'Table'[date] ) ) <= MONTH ( selected_m )
&& YEAR ( MAX ( 'Table'[date] ) ) = selected_y,
1,
0
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
If you want to show values before and on the selected month of the current year,
You could create a new table
monthname list =
FILTER (
ADDCOLUMNS (
CALENDARAUTO (),
"monthno", FORMAT ( [Date], "mm" ),
"monthname", FORMAT ( [Date], "mmmm" )
),
DAY ( [Date] ) = 1
)
Create a measure and add it to visual level filter
Measure =
VAR selected_m =
SELECTEDVALUE ( 'monthname list'[Date] )
RETURN
IF (
MONTH ( MAX ( 'Table'[date] ) ) <= MONTH ( selected_m )
&& YEAR ( MAX ( 'Table'[date] ) ) = YEAR ( TODAY () ),
1,
0
)
If you want to show selected year's data, you could create another table,
year list = FILTER(ADDCOLUMNS(CALENDARAUTO(),"yearname",YEAR([Date])),DAY([Date])=1)
Create another measure
Measure 2 =
VAR selected_m =
SELECTEDVALUE ( 'monthname list'[Date] )
VAR selected_y =
SELECTEDVALUE ( 'year list'[yearname] )
RETURN
IF (
MONTH ( MAX ( 'Table'[date] ) ) <= MONTH ( selected_m )
&& YEAR ( MAX ( 'Table'[date] ) ) = selected_y,
1,
0
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
There are a few ways. One is you can use ytd formula. second in the filter tab drag the date and from relative/advance option choose this year.
Year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |