Last week amount DAX doesn't work

[irenelitw629]

Hi,

Can someone take a look why my Last week sales amount DAX doesn't work?  Here is the file

What i did first is to create a Weeknum based on raw data

Weeknum = SUBSTITUTE(data[WeekInYear_CC],"2020-WK","")

Then I create a Last week DAX within same table

Last week =

VAR week = SELECTEDVALUE(data[Weeknum])

Return

CALCULATE(SUM(data[Amount]),

ALL(data),

data[Weeknum]= week -1

)

This DAX doesn't work - I tried to create a calendar table but then my main data source is direct query so i can't create a relationshop. I learnt how to do it previously but now it is not working again....Thanks for advice!

[amitchandak]

I correct datatype and logic. Please find file : https://www.dropbox.com/s/usfbwl5d65nmer3/LW%20error.pbix?dl=0

only Last week = 
VAR _week = maxx((data),data[Weeknum])-1
Return
CALCULATE(SUM(data[Amount])
,filter(ALL(data),
data[Weeknum]= _week
))

Last week = 
VAR week = SELECTEDVALUE(data[Weeknum])
Return
CALCULATE(SUM(data[Amount])
,filter(ALL(data),
data[Weeknum]= max(data[Weeknum]) -1
))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[irenelitw629]

Thank you @amitchandak It works in imported data.

However it is not working when I connected to data source in SAP HANA using direct query.

Is there any way to show last week amount in direct query? 

My data is too huge to export..

[irenelitw629]

Hi @amitchandak,

Also, when I use your file and pull in (opportunity) number field. Current DAX doesn't work anymore ( shows blank).

Can you please advice? Thanks a lot!!

[HotChilli]

The Weeknum column has the wrong datatype.

It is created from a text value and then gets used as if it is a whole number.

Change it to a number.