Dax used in Table visual giving error

[harshadrokade]

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

Dax 1-Latest year score = CALCULATE(AVERAGE(Sheet1[Value]), DATESYTD(dateadd(Sheet1[Year],0,YEAR)))

Dax 2-pevious year of latest year score = CALCULATE(AVERAGE(Sheet1[Value]), DATESYTD(dateadd(Sheet1[Year],-1,YEAR)))

Dax 3-pevious to previous year of latest year score = CALCULATE(AVERAGE(Sheet1[Value]), DATESYTD(dateadd(Sheet1[Year],-2,YEAR)))

The table works well with below filters-

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[harshadrokade]

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.

[Thejeswar]

Hi @harshadrokade ,

You can use a CALENDARAUTO() function, instead of CALENDAR() to auto extend the dates without doing anything in the upcoming years. 

Regards,

[Ashish_Mathur]

You are welcome.  The Calendar Table will auto extend as data gets added to the Data Table.

[harshadrokade]

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.

[Ashish_Mathur]

Hi,

Could you share a sample dataset with me where my suggested formula does not work.

[Thejeswar]

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?

[harshadrokade]

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?