Hi All, I hav ebelow data- StudentYearExam typeSubject1Subject2Subject3Subject4Subject5ABC31-Mar-21Final5244534553ABC31-Mar-21Mid7367734749ABC31-Mar-20Final4182767545ABC31-Mar-20Mid6881787189ABC31-Mar-19Final6584785740ABC31-Mar-19Mid8755724184ABC31-Mar-18Final8190484385ABC31-Mar-18Mid8577628841ABC31-Mar-17Final8245704249ABC31-Mar-17Mid5362796770ABC31-Mar-16Final4949454742ABC31-Mar-16Mid6444784066ABC31-Mar-15Final6547894059ABC31-Mar-15Mid8766904259ABC31-Mar-14Final8075416354ABC31-Mar-14Mid7342554262ABC31-Mar-13Final5355526040ABC31-Mar-13Mid8577838178ABC31-Mar-12Final4780407458ABC31-Mar-12Mid4677738490ABC31-Mar-11Final5940665650ABC31-Mar-11Mid4059654188ABC31-Mar-10Final5561555968ABC31-Mar-10Mid6373666371XYZ31-Mar-20Final8380858582XYZ31-Mar-20Mid8145469089XYZ31-Mar-19Final8071758347XYZ31-Mar-19Mid8073767354XYZ31-Mar-18Final8582435562XYZ31-Mar-18Mid6861618063XYZ31-Mar-17Final5445854876XYZ31-Mar-17Mid5059547050XYZ31-Mar-16Final6777766973XYZ31-Mar-16Mid5667505365XYZ31-Mar-15Final6172796965XYZ31-Mar-15Mid8379764056XYZ31-Mar-14Final7067728141XYZ31-Mar-14Mid7940449041XYZ31-Mar-13Final8149845868XYZ31-Mar-12Final8366665860XYZ31-Mar-11Final8360427148XYZ31-Mar-11Mid5044425245XYZ31-Mar-10Final8379436240XYZ31-Mar-10Mid8055447673 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) StudentYearExam typeAttributeValueABC31-Mar-21FinalSubject152ABC31-Mar-21FinalSubject244ABC31-Mar-21FinalSubject353ABC31-Mar-21FinalSubject445 I have created a table visual as below by adding dax for below dataDax 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

Hi, You may download my PBI file from here. Hope this helps.

harshadrokade

Post Partisan

4 years ago

Solved

Dax used in Table visual giving error

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