cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MacJasem
Helper II
Helper II

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

  


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
MacJasem
Helper II
Helper II

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

  


@ 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!:
Mastering Power BI 2nd Edition

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!! 🙂


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors