Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
harshadrokade
Post Partisan
Post Partisan

Dax used in Table visual giving error

Hi All,

 

I hav ebelow data-

 

StudentYearExam typeSubject1Subject2Subject3Subject4Subject5
ABC31-Mar-21Final5244534553
ABC31-Mar-21Mid7367734749
ABC31-Mar-20Final4182767545
ABC31-Mar-20Mid6881787189
ABC31-Mar-19Final6584785740
ABC31-Mar-19Mid8755724184
ABC31-Mar-18Final8190484385
ABC31-Mar-18Mid8577628841
ABC31-Mar-17Final8245704249
ABC31-Mar-17Mid5362796770
ABC31-Mar-16Final4949454742
ABC31-Mar-16Mid6444784066
ABC31-Mar-15Final6547894059
ABC31-Mar-15Mid8766904259
ABC31-Mar-14Final8075416354
ABC31-Mar-14Mid7342554262
ABC31-Mar-13Final5355526040
ABC31-Mar-13Mid8577838178
ABC31-Mar-12Final4780407458
ABC31-Mar-12Mid4677738490
ABC31-Mar-11Final5940665650
ABC31-Mar-11Mid4059654188
ABC31-Mar-10Final5561555968
ABC31-Mar-10Mid6373666371
XYZ31-Mar-20Final8380858582
XYZ31-Mar-20Mid8145469089
XYZ31-Mar-19Final8071758347
XYZ31-Mar-19Mid8073767354
XYZ31-Mar-18Final8582435562
XYZ31-Mar-18Mid6861618063
XYZ31-Mar-17Final5445854876
XYZ31-Mar-17Mid5059547050
XYZ31-Mar-16Final6777766973
XYZ31-Mar-16Mid5667505365
XYZ31-Mar-15Final6172796965
XYZ31-Mar-15Mid8379764056
XYZ31-Mar-14Final7067728141
XYZ31-Mar-14Mid7940449041
XYZ31-Mar-13Final8149845868
XYZ31-Mar-12Final8366665860
XYZ31-Mar-11Final8360427148
XYZ31-Mar-11Mid5044425245
XYZ31-Mar-10Final8379436240
XYZ31-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 typeAttributeValue
ABC31-Mar-21FinalSubject152
ABC31-Mar-21FinalSubject244
ABC31-Mar-21FinalSubject353
ABC31-Mar-21FinalSubject445

 

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-
harshadrokade_0-1646508218825.png

 

But when I select filters as below, it throws error as below-

harshadrokade_1-1646508248131.png

 

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

 

harshadrokade_2-1646508301031.png

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Thejeswar
Resident Rockstar
Resident Rockstar

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?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.