Need help with measure

[anky86]

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

[v-danhe-msft]

Hi @anky86 ,

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

[anky86]

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

[anky86]

BUMP

[anky86]

Guys anyone have any suggestions.

[anky86]

Guys any inputs