Forum Discussion

irenelitw629's avatar
irenelitw629
Icon for Helper II rankHelper II
6 years ago

Last week amount DAX doesn't work

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_...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    irenelitw629
    Icon for Helper II rankHelper II
    6 years ago

    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's avatar
      irenelitw629
      Icon for Helper II rankHelper II
      6 years ago

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