Forum Discussion
Need help with measure
Hi guys,
I am trying to create a measure that would display monthend values on bar chart over time. I have a slicer on top for the users to select the date where the start date is fixed and end date is the newest date in the data.
On the bar chart I want to display month end for all the years and the end date in the slicer which won't be a month end. I am trying to achieve the same for financial year end where financial year end is 30th june.
So for example, if the dates in the slicer goes from 1st jan 2018 to 24th April, the monthly bar chart shuold have
31st Jan 2018, 28thFeb 2018 to 24th April 2019. And financial year end chart would have 30th june 2018, 24th April 2019.
Here is some sample data. Thanks for your help.
| 23/10/2018 | A | 3.06 | 13.92 |
| 24/10/2018 | A | 3.07 | 13.93 |
| 25/10/2018 | A | 3.07 | 13.94 |
| 26/10/2018 | A | 3.07 | 13.95 |
| 27/10/2018 | A | 3.07 | 13.96 |
| 28/10/2018 | A | 3.07 | 13.97 |
| 29/10/2018 | A | 3.08 | 13.98 |
| 30/10/2018 | A | 3.08 | 13.99 |
| 31/10/2018 | A | 3.08 | 14 |
| 1/11/2018 | A | 3.08 | 14.01 |
| 2/11/2018 | A | 3.08 | 14.02 |
| 3/11/2018 | A | 3.09 | 14.03 |
| 4/11/2018 | A | 3.09 | 14.04 |
| 5/11/2018 | A | 3.09 | 14.05 |
| 6/11/2018 | A | 3.09 | 14.06 |
| 7/11/2018 | A | 3.10 | 14.07 |
| 8/11/2018 | A | 3.10 | 14.08 |
| 9/11/2018 | A | 3.10 | 14.09 |
| 10/11/2018 | A | 3.10 | 14.1 |
| 11/11/2018 | A | 3.10 | 14.11 |
| 12/11/2018 | A | 3.11 | 14.12 |
| 13/11/2018 | A | 3.11 | 14.13 |
| 14/11/2018 | A | 3.11 | 14.14 |
| 15/11/2018 | A | 3.11 | 14.15 |
| 16/11/2018 | A | 3.11 | 14.16 |
| 17/11/2018 | A | 3.12 | 14.17 |
| 18/11/2018 | A | 3.12 | 14.18 |
| 19/11/2018 | A | 3.12 | 14.19 |
| 20/11/2018 | A | 3.12 | 14.2 |
| 21/11/2018 | A | 3.13 | 14.21 |
| 22/11/2018 | A | 3.13 | 14.22 |
| 23/11/2018 | A | 3.13 | 14.23 |
| 24/11/2018 | A | 3.13 | 14.24 |
| 25/11/2018 | A | 3.13 | 14.25 |
| 26/11/2018 | A | 3.14 | 14.26 |
| 27/11/2018 | A | 3.14 | 14.27 |
| 28/11/2018 | A | 3.14 | 14.28 |
| 29/11/2018 | A | 3.14 | 14.29 |
| 30/11/2018 | A | 3.14 | 14.3 |
| 1/12/2018 | A | 3.15 | 14.31 |
| 2/12/2018 | A | 3.15 | 14.32 |
| 3/12/2018 | A | 3.15 | 14.33 |
| 4/12/2018 | A | 3.15 | 14.34 |
| 5/12/2018 | A | 3.16 | 14.35 |
| 6/12/2018 | A | 3.16 | 14.36 |
| 7/12/2018 | A | 3.16 | 14.37 |
| 8/12/2018 | A | 3.16 | 14.38 |
| 9/12/2018 | A | 3.16 | 14.39 |
| 10/12/2018 | A | 3.17 | 14.4 |
| 11/12/2018 | A | 3.17 | 14.41 |
| 12/12/2018 | A | 3.17 | 14.42 |
| 13/12/2018 | A | 3.17 | 14.43 |
| 14/12/2018 | A | 3.17 | 14.44 |
| 15/12/2018 | A | 3.18 | 14.45 |
| 16/12/2018 | A | 3.18 | 14.46 |
| 17/12/2018 | A | 3.18 | 14.47 |
| 18/12/2018 | A | 3.18 | 14.48 |
| 19/12/2018 | A | 3.18 | 14.49 |
| 20/12/2018 | A | 3.19 | 14.5 |
| 21/12/2018 | A | 3.19 | 14.51 |
| 22/12/2018 | A | 3.19 | 14.52 |
| 23/12/2018 | A | 3.19 | 14.53 |
| 24/12/2018 | A | 3.20 | 14.54 |
| 25/12/2018 | A | 3.20 | 14.55 |
| 26/12/2018 | A | 3.20 | 14.56 |
| 27/12/2018 | A | 3.20 | 14.57 |
| 28/12/2018 | A | 3.20 | 14.58 |
| 29/12/2018 | A | 3.21 | 14.59 |
| 30/12/2018 | A | 3.21 | 14.6 |
| 31/12/2018 | A | 3.21 | 14.61 |
| 1/01/2019 | A | 3.21 | 14.62 |
| 2/01/2019 | A | 3.21 | 14.63 |
| 3/01/2019 | A | 3.22 | 14.64 |
| 4/01/2019 | A | 3.22 | 14.65 |
| 5/01/2019 | A | 3.22 | 14.66 |
| 6/01/2019 | A | 3.22 | 14.67 |
| 7/01/2019 | A | 3.23 | 14.68 |
| 8/01/2019 | A | 3.23 | 14.69 |
| 9/01/2019 | A | 3.23 | 14.7 |
| 10/01/2019 | A | 3.23 | 14.71 |
| 11/01/2019 | A | 3.23 | 14.72 |
| 12/01/2019 | A | 3.24 | 14.73 |
| 13/01/2019 | A | 3.24 | 14.74 |
| 14/01/2019 | A | 3.24 | 14.75 |
| 15/01/2019 | A | 3.24 | 14.76 |
| 16/01/2019 | A | 3.24 | 14.77 |
| 17/01/2019 | A | 3.25 | 14.78 |
| 18/01/2019 | A | 3.25 | 14.79 |
| 19/01/2019 | A | 3.25 | 14.8 |
| 20/01/2019 | A | 3.25 | 14.81 |
| 21/01/2019 | A | 3.26 | 14.82 |
| 22/01/2019 | A | 3.26 | 14.83 |
| 23/01/2019 | A | 3.26 | 14.84 |
| 24/01/2019 | A | 3.26 | 14.85 |
| 25/01/2019 | A | 3.26 | 14.86 |
| 26/01/2019 | A | 3.27 | 14.87 |
| 27/01/2019 | A | 3.27 | 14.88 |
| 28/01/2019 | A | 3.27 | 14.89 |
| 29/01/2019 | A | 3.27 | 14.9 |
| 30/01/2019 | A | 3.27 | 14.91 |
| 31/01/2019 | A | 3.28 | 14.92 |
| 1/02/2019 | A | 3.28 | 14.93 |
| 2/02/2019 | A | 3.28 | 14.94 |
| 3/02/2019 | A | 3.28 | 14.95 |
| 4/02/2019 | A | 3.29 | 14.96 |
| 5/02/2019 | A | 3.29 | 14.97 |
| 6/02/2019 | A | 3.29 | 14.98 |
| 7/02/2019 | A | 3.29 | 14.99 |
| 8/02/2019 | A | 3.29 | 15 |
| 9/02/2019 | A | 3.30 | 15.01 |
| 10/02/2019 | A | 3.30 | 15.02 |
| 11/02/2019 | A | 3.30 | 15.03 |
| 12/02/2019 | A | 3.30 | 15.04 |
| 13/02/2019 | A | 3.30 | 15.05 |
| 14/02/2019 | A | 3.31 | 15.06 |
| 15/02/2019 | A | 3.31 | 15.07 |
| 16/02/2019 | A | 3.31 | 15.08 |
| 17/02/2019 | A | 3.31 | 15.09 |
| 18/02/2019 | A | 3.32 | 15.1 |
| 19/02/2019 | A | 3.32 | 15.11 |
| 20/02/2019 | A | 3.32 | 15.12 |
| 21/02/2019 | A | 3.32 | 15.13 |
| 22/02/2019 | A | 3.32 | 15.14 |
| 23/02/2019 | A | 3.33 | 15.15 |
| 24/02/2019 | A | 3.33 | 15.16 |
| 25/02/2019 | A | 3.33 | 15.17 |
| 26/02/2019 | A | 3.33 | 15.18 |
| 27/02/2019 | A | 3.33 | 15.19 |
| 28/02/2019 | A | 3.34 | 15.2 |
| 1/03/2019 | A | 3.34 | 15.21 |
| 2/03/2019 | A | 3.34 | 15.22 |
| 3/03/2019 | A | 3.34 | 15.23 |
| 4/03/2019 | A | 3.35 | 15.24 |
| 5/03/2019 | A | 3.35 | 15.25 |
| 6/03/2019 | A | 3.35 | 15.26 |
| 7/03/2019 | A | 3.35 | 15.27 |
| 8/03/2019 | A | 3.35 | 15.28 |
| 9/03/2019 | A | 3.36 | 15.29 |
| 10/03/2019 | A | 3.36 | 15.3 |
| 11/03/2019 | A | 3.36 | 15.31 |
| 12/03/2019 | A | 3.36 | 15.32 |
| 13/03/2019 | A | 3.36 | 15.33 |
| 14/03/2019 | A | 3.37 | 15.34 |
| 15/03/2019 | A | 3.37 | 15.35 |
| 16/03/2019 | A | 3.37 | 15.36 |
| 17/03/2019 | A | 3.37 | 15.37 |
| 18/03/2019 | A | 3.38 | 15.38 |
| 19/03/2019 | A | 3.38 | 15.39 |
| 20/03/2019 | A | 3.38 | 15.4 |
| 21/03/2019 | A | 3.38 | 15.41 |
| 22/03/2019 | A | 3.38 | 15.42 |
| 23/03/2019 | A | 3.39 | 15.43 |
| 24/03/2019 | A | 3.39 | 15.44 |
| 25/03/2019 | A | 3.39 | 15.45 |
| 26/03/2019 | A | 3.39 | 15.46 |
| 27/03/2019 | A | 3.39 | 15.47 |
| 28/03/2019 | A | 3.40 | 15.48 |
| 29/03/2019 | A | 3.40 | 15.49 |
| 30/03/2019 | A | 3.40 | 15.5 |
| 31/03/2019 | A | 3.40 | 15.51 |
| 1/04/2019 | A | 3.41 | 15.52 |
| 2/04/2019 | A | 3.41 | 15.53 |
| 3/04/2019 | A | 3.41 | 15.54 |
| 4/04/2019 | A | 3.41 | 15.55 |
| 5/04/2019 | A | 3.41 | 15.56 |
| 6/04/2019 | A | 3.42 | 15.57 |
| 7/04/2019 | A | 3.42 | 15.58 |
| 8/04/2019 | A | 3.42 | 15.59 |
| 9/04/2019 | A | 3.42 | 15.6 |
| 10/04/2019 | A | 3.42 | 15.61 |
| 11/04/2019 | A | 3.43 | 15.62 |
| 12/04/2019 | A | 3.43 | 15.63 |
| 13/04/2019 | A | 3.43 | 15.64 |
| 14/04/2019 | A | 3.43 | 15.65 |
| 15/04/2019 | A | 3.43 | 15.66 |
| 16/04/2019 | A | 3.44 | 15.67 |
| 17/04/2019 | A | 3.44 | 15.68 |
| 18/04/2019 | A | 3.44 | 15.69 |
| 19/04/2019 | A | 3.44 | 15.7 |
| 20/04/2019 | A | 3.45 | 15.71 |
| 21/04/2019 | A | 3.45 | 15.72 |
| 22/04/2019 | A | 3.45 | 15.73 |
| 23/04/2019 | A | 3.45 | 15.74 |
| 24/04/2019 | A | 3.45 | 15.75 |
| 25/04/2019 | A | 3.46 | 15.76 |
| 26/04/2019 | A | 3.46 | 15.77 |
| 27/04/2019 | A | 3.46 | 15.78 |
| 28/04/2019 | A | 3.46 | 15.79 |
5 Replies
v-danhe-msftMicrosoft Employee
Hi Anonymous ,
I could not understand your below word:
if the dates in the slicer goes from 1st jan 2018 to 24th April, the monthly bar chart shuold have
31st Jan 2018, 28thFeb 2018 to 24th April 2019. And financial year end chart would have 30th june 2018, 24th April 2019.
Could you please post your desired result if possible?
Regards,
Daniel He
Hi v-danhe-msft
So in the current sample, my data range from 23/10/2018 to 27/04/2019. To get a flag for month end, I have created a calculated column
IsMonthEnd = IF((Sheet1[Date]= EOMONTH(Sheet1[Date],0))|| (Sheet1[Date]=MAX(Sheet1[Date])),"Yes", "No")
which creates the following chart
Now when I change the slicer to reflect the end date as 27/04/2019, the graph looks like below
It ends on 31st march 2019 as the calculated column wouln't reflect this as month end which is correct. I am trying to ad an addtional bar to the above chart where it would show month end on 27th April 2019 as that is the end date in slicer which would require a measure. So even if I change the end date in slicer to 26th April 2019 then the last bar on the chart would show 26th April's result.
I hope that clarifies a bit.
Thanks