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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
punnas1
Helper II
Helper II

Dax syntax error

I'm new to dax
Pls help with error debug.
I'm trying to calculate sum of sales increase 19 vs 20 by Product and customer.
Positive diff =
Var a = distinct (sales[product])
Var b = district ( sales [customer])
Var c = a&b
Return
Sumx ( c, max(0,[val diff 19vs20]))

Thank you.

3 REPLIES 3
AntrikshSharma
Super User
Super User

@punnas1  You are using DISTRICT instead of DISTINCT, the code that you have written won't work as both columns have a different data lienage and you will have to either SUM them differently then add or manually inject filters after Union which will be complex for you at your currentl level of DAX experience. For now I would suggest that you write something like this:

 

Positive diff =
VAR a =
    DISTINCT ( sales[product] )
VAR b =
    DISTINCT ( sales[customer] )
VAR Result =
    SUMX ( a, MAX ( 0, [val diff 19vs20] ) )
        + SUMX ( b, MAX ( 0, [val diff 19vs20] ) )
RETURN
    Result

 

amitchandak
Super User
Super User

@punnas1 , Data is not sufficient. You can use date table and time intelligence

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


//Only year vs Year, not a level below

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 ])

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Fowmy
Super User
Super User

@punnas1 

Really not enough information to understand your calculation and not sure about the error you received.

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.