Forum Discussion
Dynamic YTD with Month slicer
Hi all,
I'm tring to create a column chart showing cumulative YTD Sales by month. My data include a Sales table, a Dim_Date table related to the Sales table, and a disconnected Slicer_Date table which = Dim_Date.
After I build the cumulative YTD Column chart:
I want to introduce Year and Month slicers so that if I choose a specific month, the column chart should show columns up to that month. For instance if I choose September, I would expect this:
Or in a video I watched, the rest months shows same value as September, and this would also work for me:
However, right now if I choose September, it only shows one bar:
My YTD Calculation is:
How should I change the measure?
Hi WaninGNighT
Thanks for your pbix file, I can't reply to private messages due to policy reasons, please refer to the solution below, I hope it helps.
1. You should create the slicer using the 'Year' and 'Month' fields from the disconnected date table_'Slicer Date', rather than the fields in the 'Dim Date' table.
2. Create the following measures:YTD/QTD/MTD = SWITCH( SELECTEDVALUE('Time Intelligence'[Period]), "YTD", CALCULATE([Total Sales Amount], DATESYTD(Dim_Date[Date])), "QTD", CALCULATE([Total Sales Amount], DATESQTD(Dim_Date[Date])), "MTD", CALCULATE([Total Sales Amount], DATESMTD(Dim_Date[Date])) )New YTD/QTD/MTD = VAR SelectedYear = SELECTEDVALUE(Slicer_Date[Year]) VAR SelectedMonth = SELECTEDVALUE(Slicer_Date[Month]) RETURN CALCULATE( [YTD/QTD/MTD], FILTER( Dim_Date, Dim_Date[Year] = SelectedYear && Dim_Date[Month] <= SelectedMonth ) )
3. Place the 'Month' or 'Month Name' field from the 'Dim Date' table and the measure 'New YTD/QTD/MTD' into the column chart.Here are my test results:
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- anilelmastasi
Super User
Hello WaninGNighT ,
Your current YTD measure is not working as expected because of how you are filtering Dim_Date. The issue arises from the fact that when you select a month, it only returns data for that single month instead of accumulating values up to that month.
YTD Sales =
VAR SelectedYear = SELECTEDVALUE ( 'Slicer_Date'[Year] )
VAR SelectedMonth = SELECTEDVALUE ( 'Slicer_Date'[Month] )RETURN
CALCULATE (
[Total Sales Amount], -- Your total sales measure
FILTER (
ALL ( 'Dim_Date' ), -- Ensures all dates are considered
'Dim_Date'[Year] = SelectedYear &&
'Dim_Date'[Month] <= SelectedMonth
)
)Previously, you were using MAX ( 'Dim_Date'[Month] ), which caused the measure to only return values for the selected month. Now, we make sure to include all months up to the selected month.
ALL ( 'Dim_Date' ) ensures that the measure evaluates all months in the selected year, rather than just those currently selected in the visual.
If this answer helpful for you, select as an answer and kudos please.
Thank you!
Hello anilelmastasi ,
Thank you for your answer, but it didn't solve my question. I tried your way of calculating measure ytd, when I select a month, the column chart still only shows one bar which is the selected month. The calculation of cumulative sales is correct, but I just want all the previous months to show on x-axis as well.
Hi WaninGNighT
Please make sure you use the Month field in the Dim_Date table as the X-axis in the column chart instead of the Month field in the Slicer_Date table.
I did a simple test based on your description and can get what you want using your measure. For details, please see my pbix file.Total Sales Amount = CALCULATE( SUM(Sales[Sales_Amount]), DATESYTD(Dim_Date[Date]) )YTD = VAR M = SELECTEDVALUE ( 'Slicer_Date'[Month] ) VAR Y = SELECTEDVALUE ( 'Slicer_Date'[Year] ) RETURN CALCULATE ( [Total Sales Amount], FILTER ( ALL ( 'Dim_Date' ), 'Dim_Date'[Year] = Y && 'Dim_Date'[Month] <= M && 'Dim_Date'[Month] <= MAX ( 'Dim_Date'[Month] ) ) )Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you Anonymous, I also sent you a private message asking further questions.
I tried your method but it didn't quite work for me.
I'm actually trying to do the same thing as this video shows: https://www.youtube.com/watch?v=DroZHtYA1-E but I stucked at the first step.
I used exactly the same measure, and also created the toggle button:
and if I choose September like the video, it only shows me one bar, which make me really confused:
Hi WaninGNighT
Thanks for your pbix file, I can't reply to private messages due to policy reasons, please refer to the solution below, I hope it helps.
1. You should create the slicer using the 'Year' and 'Month' fields from the disconnected date table_'Slicer Date', rather than the fields in the 'Dim Date' table.
2. Create the following measures:YTD/QTD/MTD = SWITCH( SELECTEDVALUE('Time Intelligence'[Period]), "YTD", CALCULATE([Total Sales Amount], DATESYTD(Dim_Date[Date])), "QTD", CALCULATE([Total Sales Amount], DATESQTD(Dim_Date[Date])), "MTD", CALCULATE([Total Sales Amount], DATESMTD(Dim_Date[Date])) )New YTD/QTD/MTD = VAR SelectedYear = SELECTEDVALUE(Slicer_Date[Year]) VAR SelectedMonth = SELECTEDVALUE(Slicer_Date[Month]) RETURN CALCULATE( [YTD/QTD/MTD], FILTER( Dim_Date, Dim_Date[Year] = SelectedYear && Dim_Date[Month] <= SelectedMonth ) )
3. Place the 'Month' or 'Month Name' field from the 'Dim Date' table and the measure 'New YTD/QTD/MTD' into the column chart.Here are my test results:
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
