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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MacJasem
Helper III
Helper III

7 Day Running Total and other DAX measures keeps returning an error

Hi Guys

 

I've experienced that I seem to have an issue with writing Dax measures. I keep getting errors when refering to table Columns.

 

What am i doing wrong? is there a setting that i have to change somewhere?

 

7DayRunningTotal = CALCULATE( [SalesValue],
FILTER(ALL('Date', 'Date'[Date] > MAX('Date'[Date]) - 7 && 'Date'[Date] <= MAX('Date'[Date]))))
 
as I find inspiration from other Dax measures shared on the net I see the reference to Tables is different in my case with the '' around the table, whereas I dont see this in other peoples DAX examples.
 
MacJasem_0-1664440434273.png
 
 
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@MacJasem Missed a closing paren: ALL('Date'), 

7DayRunningTotal = 
CALCULATE( 
  [SalesValue],
  FILTER(
    ALL('Date'), 
    'Date'[Date] > MAX('Date'[Date]) - 7 && 'Date'[Date] <= MAX('Date'[Date])
  )
)

  


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
MacJasem
Helper III
Helper III

Hi @Greg_Deckler 

 

I got inspiration from your colleague Sam McKay (Enterprise DNA) with regards to calculating previous week sales:

MacJasem_0-1664894258682.png

 

This time I even used DaxFormatter to make sure nothing was missed, and the formatting came out fine, but i still got this error in PowerBI. Couldn't really figure out what the issue is. Any idea?

 

PreviousWeekSales =
VAR CurrentWeek =
SELECTEDVALUE ( 'Date'[Week Number] )
VAR CurrentYear =
SELECTEDVALUE ( 'Date'[Year] )
VAR MaxWeekNumber =
CALCULATE ( MAX ( 'Date'[Week Number] ), ALL ( 'Date' ) )
RETURN
SUMX (
FILTER (
ALL ( 'Date' ),
IF (
CurrentWeek = 1,
Date[Week Number] = MaxWeekNumber
&& 'Date'[Year] = CurrentYear - 1,
Date[Week Number] = CurrentWeek - 1
&& 'Date'[Year] = CurrentYear
)
),
[SalesValue]
)

 

Greg_Deckler
Super User
Super User

@MacJasem Missed a closing paren: ALL('Date'), 

7DayRunningTotal = 
CALCULATE( 
  [SalesValue],
  FILTER(
    ALL('Date'), 
    'Date'[Date] > MAX('Date'[Date]) - 7 && 'Date'[Date] <= MAX('Date'[Date])
  )
)

  


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

StopYouAreEmbarrassingGIF.gif

 Overthought that one without checking the formating - how embarrasing 😂

 

Thanks @Greg_Deckler, much appreciated the swift reply!🙏

 

@MacJasem Happens!! 🙂


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors