need to dynamically assign tables

[Anonymous]

Hi all, I am working with school data and per Academic year I have loads and loads of data. One of the things I am working is a dashboard in which I compare the same data over different Academic Years. I have built a dashboard that shows these data,  but here I hard-coded the tables (Eg years) I needed. However,  have not three years (soon 4) worth of data and I do not want to create a dashboard for every possible comparism. So I would like to do this dynamically.

Is there a way so assign a slicer or so to set the year 1 and year 2 (or T0 and T1) for the comparsim so that a user can select which years he/she would like to compare?

Thanks

Ubo

[amitchandak]

@Anonymous , Make sure you create a separate Year/date table and try formula's like given in example

measure

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

column in year/date table

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)  //You can use year

measure
This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

[Anonymous]

Thanks. Will have a look into this!

Ubo

[Ashish_Mathur]

Hi,

Your question is not clear.  Share some data and show the expected result.

[Anonymous]

Hi Asish,

Thanks for making this clear. I will try to elaborate. We are working with school sanitation data and we have a dashboard showing how the schools (47000 in the Philippines) are doing on different aspects (handwashing,  toothbrushing, availability of useable toilets etc.). The dashboard shows on an aggregated level (school districts and school regions) a whole set of indicators based on a 3 star approach (0-3) stars. On these data are collected yearly and the annual dashboard is now being tested and under review.

Since the data are coming in yearly, we would also give users the option to show their progress over the years. I have made a sample dashboard already in which we can compare two years. See screenshots as example

For these calculations I read data from table 1 ( = year 1) and present a table or a graph and do the same for year 2 (data in table 2). For the development and testing I have hardcoded the tables I use ( so T0 = data 2018-19 and T1 = data 2019-20). However, more and more data are coming in (we now have 3 years, new data will come each year). The way I have done it now is prepare a separate dashboard for every combination of years ( so comparing 2017 -2019 is one dashboard and 2018- - 2019 is another). 

For testing ok, but for the final product I would like to give the user more fleibility. I would like to be able to load the data we have now (so now 3 years, soon 4 years) and give the user the option (slicer I think) to select which year he/she would like to compare with which year 

Hope this makes things a bit more clear

Ubo

[Ashish_Mathur]

Hi,

Hasn't Amit's solution worked for you?

[Anonymous]

Thanks for the follow up. Got down with some stomach issues for a few days and was not yet able to try things. Now more or less up and running and will try next week. Will keep you posted

Ubo