TRICKY - Line and Bar Chart Plot

[Anonymous]

Hi Expert

I want to plot the following data on a line and clustered column chart. 

Month	Past	Total Current	Total PD %	Year
Jan-20	15.52	176.32	8.1%	2021
Feb-20	20.73	169.27	10.9%	2021
Mar-20	18.15	161.29	10.1%	2021
Apr-20	19.36	132.09	12.8%	2021
May-20	13.61	119.43	10.2%	2021
Jun-20	9.22	138.32	6.3%	2021
Jul-20	9.40	155.08	5.7%	2021
Aug-20	7.92	163.85	4.6%	2021
Sep-20	4.41	177.86	2.4%	2021
Oct-20	4.52	191.06	2.3%	2021
Nov-20	6.03	191.40	3.1%	2021
Dec-20	5.13	174.54	2.9%	2021
Jan-21	5.75	173.33	3.2%	2021
Feb-21	4.94	175.92	2.7%	2021
	Bar	Bar	Line

The Issue is if the user select from the Month Filter Jan 20 i want to only Plot the Data upto Jan 21 (ignore feb 21) likewise if the user selects Feb 20 then plot upto Feb 21 and if we had data for Mar 21 then ignore.... When i get and include Mar 20 data the plot upto Mar 21....and so on...

[sayaliredij · Accepted solution]

1. Instead of using Month Name like 'Jan 2020 ' or 'Feb 2020', you can use date eg. First day of the month. Instead of  Jan 2020 - 1 Jan 2020. Which gives you the possibility to use some time intelligence functions.
2. Create a calculated table using the following formula
    Months = DISTINCT(Table[Month])
3.  Use Slicer for the Month from this table and not from the Original table
4.  In the original table create a measure called MonthFilter
MonthFilter = 
var selectedmonth = EDATE(DATEVALUE(SELECTEDVALUE(Months[Month])),12)
RETURN
IF(MAX(Table[Month]) <= selectedmonth,1,0)
4. Create a line chart for your data 
5. In Visual level filter, add MonthFilter and set as 1 

Regards,

Sayali

If this post helps, then please consider Accept it as the solution to help others find it more quickly.