Forum Discussion

harshadrokade's avatar
harshadrokade
Icon for Post Partisan rankPost Partisan
3 years ago

Using mapped Years to show the data on chart

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 ameIDReport dateScoring year
A128-12-2131-03-19
B228-12-2131-03-20
B229-07-2231-03-21
C329-07-2231-03-21
D429-07-2031-03-17
D431-12-2131-03-18
D427-12-2231-03-21

 

Scoring table

 

Student nameIDScoring yearSubject1Subject2Subject3Subject4
A131-03-1947434216
A131-03-2030502814
A131-03-2126453127
B231-03-1949405017
B231-03-2013382219
B231-03-2143233738
C331-03-1837184143
C331-03-1926162722
C331-03-2045214950
C331-03-2129182928
D431-03-1725334619
D431-03-1840381925
D431-03-1946141947
D431-03-2020174441
D431-03-2118154629

 

Need Help

3 Replies

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      harshadrokade
      Icon for Post Partisan rankPost Partisan
      3 years ago

      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.