Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi friends,
I have a month slicer and 2 measures including Sales_MTD and Sales_YTD, now i need to show up both in a dashboard to see:
- A Clustered Column chart to show Sales_MTD by day
- A Clustered Column chart to show Sales_YTD by month
However, when i select a month( i.e. when i select March) on the slicer i only show the Sales_YTD for March but i need show both Sales_YTD for Jan,Feb,March in a chart. If i select multiple month on slicer, the YTD chart can show correctly but the MTD-chart will incorrect. Please help me to resolve this issue- select a month to show Sales_YTD by month
Regards,
J.
Solved! Go to Solution.
In this scenario, when you select March in Slicer, the current context is March only so that the YTD calculation can't calculate for Jan and Feb. For your requirement, I suggest you create two slicers, one for YTD chart, the other for MTD. You can click the slicer and "Edit Interactions" under Format tab to determine which visual to interact.
Regards,
Hey,
not sure if I understand your question correctly, but I give it a try. Assuming that you have a Calendar Table that at least has the columns for your dates, months (1 to 12) and year. And that this table is related to your fact table you can use these calculations to get a MonthToDate value and a YearToDate value.
Please be aware that I'm using two Slicers one for the Month and one for the Year. In my opinion it is necessary to also use a Year slicer to pick just one month of a single year.
MonthToDate
MonthToDate = CALCULATE(sum('fact'[SimpleMeasure]);filter(all('Calendar');'Calendar'[Year] = max('Calendar'[Year])&& 'Calendar'[Month] = max('Calendar'[Month])))
YearToDate
YearToDate = CALCULATE(sum('fact'[SimpleMeasure]);filter(all('Calendar');'Calendar'[Year] = max('Calendar'[Year])&& 'Calendar'[Month] <= max('Calendar'[Month])))
And a screen of my report
As well as a screen of the relationships
Hope this helps
Thank you so much! but i need to show the chart not list
In this scenario, when you select March in Slicer, the current context is March only so that the YTD calculation can't calculate for Jan and Feb. For your requirement, I suggest you create two slicers, one for YTD chart, the other for MTD. You can click the slicer and "Edit Interactions" under Format tab to determine which visual to interact.
Regards,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |