Forum Discussion
How to plot YTD months on a bar chart
So, I have this month filter slicer in my report. I have another visual in a report - a bar chart. The chart plots a measure on the Y axis and Fiscal month on the X axis. The month on Y axis changes as per the month filter selection as expected.
Now, I need a functionality where instead of plotting the single month which is selected in my filter, I want to plot all YTD months for that year. For example, considering the calendar is default January to December, if I select 'March-2017' in my filter, I want the chart to plot 'Jan-2017', 'Feb-2017' and 'Mar-2017' on my X axis. If I filter on 'April-2017', I want the chart to plot 'Jan-2017', 'Feb-2017', 'Mar-2017' and 'Apr-2017' on my X axis. Let me know if you need more details to help help my situation :)
P.S - I have a date table in my model with a date column in it.
Hi amitchandra,
My apporach in this situation would be to create a disconnected table (one that doesn't have any relationships with other tables) and use a helper column.
I would create calculated column in Date table called Rolling Months.
Rolling Months = VAR MAX_DATE_ = CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) ) //calculates the max date of the table, will not be affected by on-page slicers and cross filters RETURN DATEDIFF ( 'Date'[Date], MAX_DATE_, MONTH ) //calculates the difference in months from max date, the latest month will return zeroI would create a calculated table based of my existing Date table.
Month Table = ALL ( 'Date'[Month], 'Date'[Rolling Months] )
Then I would create a measure that gets filtered based on the selection from the disconnected table and use this measure in the visuals.
MeasureYTD = CALCULATE ( SUM ( 'Fact'[Measure] ), FILTER ( 'Date', 'Date'[Rolling Months] >= [Rolling Month Selected] ) )These would result to something like this:
For the PBIX, refer to this link https://drive.google.com/file/d/1TBd7w3_bcV4E0oydgrB_ZANL60srZUBg/view?usp=sharing
5 Replies
- danextian
Super User
Would you mind posting a sample data?
Can't post the actual data but below should give an idea -
Fact Table -
DateID Measure 20170102 34 20170130 63 20170214 6345 20170325 635 20170316 824 20170421 524 Date Table -
DateID Date Month 20170102 2/01/2017 Jan 20170130 30/01/2017 Jan 20170214 14/02/2017 Feb 20170325 25/03/2017 Mar 20170316 16/03/2017 Mar 20170421 21/04/2017 Apr Relation b/w them 'Date Table'[DateID] -> 'Fact Table'[Date[ID]
I am dragging 'Fact Table'[Measure] on the Values, and 'Date Table'[Month] on the Axis.
- danextian
Hi amitchandra,
My apporach in this situation would be to create a disconnected table (one that doesn't have any relationships with other tables) and use a helper column.
I would create calculated column in Date table called Rolling Months.
Rolling Months = VAR MAX_DATE_ = CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date' ) ) //calculates the max date of the table, will not be affected by on-page slicers and cross filters RETURN DATEDIFF ( 'Date'[Date], MAX_DATE_, MONTH ) //calculates the difference in months from max date, the latest month will return zeroI would create a calculated table based of my existing Date table.
Month Table = ALL ( 'Date'[Month], 'Date'[Rolling Months] )
Then I would create a measure that gets filtered based on the selection from the disconnected table and use this measure in the visuals.
MeasureYTD = CALCULATE ( SUM ( 'Fact'[Measure] ), FILTER ( 'Date', 'Date'[Rolling Months] >= [Rolling Month Selected] ) )These would result to something like this:
For the PBIX, refer to this link https://drive.google.com/file/d/1TBd7w3_bcV4E0oydgrB_ZANL60srZUBg/view?usp=sharing
