Using mapped Years to show the data on chart

[harshadrokade]

HI friends,

I have data as below.

I have a Student & Year slicer & and a bar chart created where slicer has value selected from Mapping table > Student name, & Mapping table > Report date.

Bar chart has data showing 3 year scoring performance which is used from Scoring table. The latest year should be the year that is mapped to that respective student & report date in mapping table. 

E.g. If Student B is selected with report date as 29-07-2022 on slicer, The latest financial year to be used is as 31-03-2021

If Student D is selected with report date as 29-07-2020 on slicer, The latest financial year to be used is as 31-03-2017

How can I achive the same & how do I need to set up  the relationships. Pls help here.

Mapping table-

Student ame	ID	Report date	Scoring year
A	1	28-12-21	31-03-19
B	2	28-12-21	31-03-20
B	2	29-07-22	31-03-21
C	3	29-07-22	31-03-21
D	4	29-07-20	31-03-17
D	4	31-12-21	31-03-18
D	4	27-12-22	31-03-21

Scoring table

Student name	ID	Scoring year	Subject1	Subject2	Subject3	Subject4
A	1	31-03-19	47	43	42	16
A	1	31-03-20	30	50	28	14
A	1	31-03-21	26	45	31	27
B	2	31-03-19	49	40	50	17
B	2	31-03-20	13	38	22	19
B	2	31-03-21	43	23	37	38
C	3	31-03-18	37	18	41	43
C	3	31-03-19	26	16	27	22
C	3	31-03-20	45	21	49	50
C	3	31-03-21	29	18	29	28
D	4	31-03-17	25	33	46	19
D	4	31-03-18	40	38	19	25
D	4	31-03-19	46	14	19	47
D	4	31-03-20	20	17	44	41
D	4	31-03-21	18	15	46	29

[v-xiaosun-msft]

Hi @harshadrokade ,

If you want to display it in your bar chart, you can establish a relationship between two tables as below.

Final output:

Or you can create a measure to return the result.

The latest financial year =
VAR _slice =
    SELECTEDVALUE ( 'Mapping table'[Student ame] )
VAR _slice2 =
    SELECTEDVALUE ( 'Mapping table'[Report date] )
RETURN
    CALCULATE (
        MAX ( 'Mapping table'[Scoring year] ),
        FILTER (
            'Mapping table',
            'Mapping table'[Student ame] = _slice
                && 'Mapping table'[Report date] = _slice2
        )
    )

Output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[harshadrokade]

Thanks @v-xiaosun-msft This ws really helpful. I think I am almost there but now I am unable to show last 3 year trend on the bar chart. How to achieve the same?

E.g. In slicers, if I select D in Student name & Report date as 27-12-2022, I want to show the trend of past 3 years Subject 1 score in the bar chart i.e. of 31-03-2021, 31-03-2020 & 31-03-2019. 

In slicers, if I select D in Student name & Report date as 29-07-2020, I want to show the trend of past 3 years Subject 1 score of past 3 years, i.e. of 31-03-2017, 31-03-2016 & 31-03-2015 (As there is no data for 2016 & 2015, the chart will show only 2017 for this slicer selections). 

I converted the stack bar chart into clustered column chart, used the Scoring table>Scoring year on X axis & Scoring table>Subject1 score on Y axis & then tried to use your measure as filter in the chart but its not giving me the last three years data on the chart.

[harshadrokade]

@v-xiaosun-msft HI, Can you pls help here