March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi All,
I hav ebelow data-
Student | Year | Exam type | Subject1 | Subject2 | Subject3 | Subject4 | Subject5 |
ABC | 31-Mar-21 | Final | 52 | 44 | 53 | 45 | 53 |
ABC | 31-Mar-21 | Mid | 73 | 67 | 73 | 47 | 49 |
ABC | 31-Mar-20 | Final | 41 | 82 | 76 | 75 | 45 |
ABC | 31-Mar-20 | Mid | 68 | 81 | 78 | 71 | 89 |
ABC | 31-Mar-19 | Final | 65 | 84 | 78 | 57 | 40 |
ABC | 31-Mar-19 | Mid | 87 | 55 | 72 | 41 | 84 |
ABC | 31-Mar-18 | Final | 81 | 90 | 48 | 43 | 85 |
ABC | 31-Mar-18 | Mid | 85 | 77 | 62 | 88 | 41 |
ABC | 31-Mar-17 | Final | 82 | 45 | 70 | 42 | 49 |
ABC | 31-Mar-17 | Mid | 53 | 62 | 79 | 67 | 70 |
ABC | 31-Mar-16 | Final | 49 | 49 | 45 | 47 | 42 |
ABC | 31-Mar-16 | Mid | 64 | 44 | 78 | 40 | 66 |
ABC | 31-Mar-15 | Final | 65 | 47 | 89 | 40 | 59 |
ABC | 31-Mar-15 | Mid | 87 | 66 | 90 | 42 | 59 |
ABC | 31-Mar-14 | Final | 80 | 75 | 41 | 63 | 54 |
ABC | 31-Mar-14 | Mid | 73 | 42 | 55 | 42 | 62 |
ABC | 31-Mar-13 | Final | 53 | 55 | 52 | 60 | 40 |
ABC | 31-Mar-13 | Mid | 85 | 77 | 83 | 81 | 78 |
ABC | 31-Mar-12 | Final | 47 | 80 | 40 | 74 | 58 |
ABC | 31-Mar-12 | Mid | 46 | 77 | 73 | 84 | 90 |
ABC | 31-Mar-11 | Final | 59 | 40 | 66 | 56 | 50 |
ABC | 31-Mar-11 | Mid | 40 | 59 | 65 | 41 | 88 |
ABC | 31-Mar-10 | Final | 55 | 61 | 55 | 59 | 68 |
ABC | 31-Mar-10 | Mid | 63 | 73 | 66 | 63 | 71 |
XYZ | 31-Mar-20 | Final | 83 | 80 | 85 | 85 | 82 |
XYZ | 31-Mar-20 | Mid | 81 | 45 | 46 | 90 | 89 |
XYZ | 31-Mar-19 | Final | 80 | 71 | 75 | 83 | 47 |
XYZ | 31-Mar-19 | Mid | 80 | 73 | 76 | 73 | 54 |
XYZ | 31-Mar-18 | Final | 85 | 82 | 43 | 55 | 62 |
XYZ | 31-Mar-18 | Mid | 68 | 61 | 61 | 80 | 63 |
XYZ | 31-Mar-17 | Final | 54 | 45 | 85 | 48 | 76 |
XYZ | 31-Mar-17 | Mid | 50 | 59 | 54 | 70 | 50 |
XYZ | 31-Mar-16 | Final | 67 | 77 | 76 | 69 | 73 |
XYZ | 31-Mar-16 | Mid | 56 | 67 | 50 | 53 | 65 |
XYZ | 31-Mar-15 | Final | 61 | 72 | 79 | 69 | 65 |
XYZ | 31-Mar-15 | Mid | 83 | 79 | 76 | 40 | 56 |
XYZ | 31-Mar-14 | Final | 70 | 67 | 72 | 81 | 41 |
XYZ | 31-Mar-14 | Mid | 79 | 40 | 44 | 90 | 41 |
XYZ | 31-Mar-13 | Final | 81 | 49 | 84 | 58 | 68 |
XYZ | 31-Mar-12 | Final | 83 | 66 | 66 | 58 | 60 |
XYZ | 31-Mar-11 | Final | 83 | 60 | 42 | 71 | 48 |
XYZ | 31-Mar-11 | Mid | 50 | 44 | 42 | 52 | 45 |
XYZ | 31-Mar-10 | Final | 83 | 79 | 43 | 62 | 40 |
XYZ | 31-Mar-10 | Mid | 80 | 55 | 44 | 76 | 73 |
As I want to create a table visual, I have unpivot the same data in Power bi And so it looks in below format- (Sample rows shows below)
Student | Year | Exam type | Attribute | Value |
ABC | 31-Mar-21 | Final | Subject1 | 52 |
ABC | 31-Mar-21 | Final | Subject2 | 44 |
ABC | 31-Mar-21 | Final | Subject3 | 53 |
ABC | 31-Mar-21 | Final | Subject4 | 45 |
I have created a table visual as below by adding dax for below data
But when I select filters as below, it throws error as below-
When clicked on 'See details', it shows error as below. I think I am using wrong dax to generate latest year, previous year & previous to previous year score. My dates are not in sequense for all the students & there will be gaps in between & so DATEADD is not working. Plssssssssssssss help me to get correct dax to get these three years values with shared data which will be different for different students. Thanks a lot for all your help
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks @Ashish_Mathur . I saw that the calendar year table has year only till 2021. my data will keep adding for upcoming years liek 2022, 2023 & so on..
In that case I want the latest year to be considered as per latest data & not 2021. How to manage that sir. Pls help.
Hi @harshadrokade ,
You can use a CALENDARAUTO() function, instead of CALENDAR() to auto extend the dates without doing anything in the upcoming years.
Regards,
You are welcome. The Calendar Table will auto extend as data gets added to the Data Table.
Thanks a lot. I saw that & its working as expected.
The only issue that in case my dates table has dates as below, it brings 2021, 2020 & 2019 year value.
2021
2020
2018
I want the latest year value, previous year of latest year value & previous to previous year values from database. In above case, I want 2021, 2020 & 2018 value but its giving third year as 2019.
If you give me dax to get max value, max-1 value & max-2 value from database, it will be very helpful.
Thanks a lot.
Hi,
Could you share a sample dataset with me where my suggested formula does not work.
Hi @harshadrokade ,
The Error says the column that you have used in the DATEADD() function is not having continuous dates and there are gaps in the column
Can you chk on it?
Thanks @Thejeswar . I could see that but my data is that way only. The data is not continous & there are gaps in dates, I can;t change that.
Can you pls suggest me a correct measure (Instead of Datesadd) that will give me correct values with available data?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |