Forum Discussion
TRICKY - Line and Bar Chart Plot
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...
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 1Regards,
Sayali
If this post helps, then please consider Accept it as the solution to help others find it more quickly.
1 Reply
- sayaliredij
Solution Sage
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 1Regards,
Sayali
If this post helps, then please consider Accept it as the solution to help others find it more quickly.