Issues with Lookupvalue and EDATE return blank

[Zorgonaute84]

Hello,

I'm wrting here because I spent a lot hours to solve my problem and I give up... It's 2AM, and I'm so tired 🙂

I'm looking to compare sales between the current month and the previous one. But it's not easy at it looks...

I have a first table "Sales and Warranty" with all my sales. The table include the fields :

- Ref.Article (String), it's the ID of the product
- Sales Last 24 Mths (Integer) it's a calculated value from SAP BW wich represent the cumulated sales of the last 24 months at the moment
- Date Month Year (String)... Yes it's not a date field, but a String. The format is "JAN 2021"

So this table look something like that :

On the other hand, I have a Date table with this fields :

- Date (DateTime generate in DAX)

- MonthDateSAP (String with the exact same format of the "Date Month Year")

So, I want to add a new column to the Sales and Warranty table wich represent the Sales Last 24 Mths for the previous period.

This is the DAX code I wrote :

Sales Last 24 Mths (n-1) =
var Article = 'Sales and Warranty'[Ref.Article]
var FirstDayOfMonth =
CALCULATE(
FIRSTDATE('Date'[Date]),
FILTER('Date','Date'[MonthDateSAP]='Sales and Warranty'[Date Month Year]))
var FirstDayPrevMonth = EDATE(FirstDayOfMonth,-1)
var SAPMonth =
LOOKUPVALUE('Date'[MonthDateSAP],'Date'[Date],FirstDayPrevMonth)
return
CALCULATE(
SUM('Sales and Warranty'[Sales Last 24 Mths]),
FILTER('Sales and Warranty',
'Sales and Warranty'[Date Month Year] = SAPMonth && 'Sales and Warranty'[Ref.Article]=Article))

The idea is to "transform" the string value JAN 2021 into a DateTime 01.01.2021 (thanks to the DateTable),

then minus one month,

then use the lookup function to "convert" the Date into the good String value (DEC 2020)

finally, calculate in the context with the different filters (Article and String Date)

But this is returning me a blank result... 😞

The strange thing is, if I set to 0 the parameter of the EDATE function, it's works, but it's returning me the current sales and not the previous one...

I had some test in other column, just to try the conversion, but same result :

Prev Month Year =
var FirstDayOfMonth =
CALCULATE(
FIRSTDATE('Date'[Date]),
FILTER('Date','Date'[MonthDateSAP]='Sales and Warranty'[Date Month Year]))
var FirstDayPrevMonth = EDATE(FirstDayOfMonth,-1)
var SAPMonth =
LOOKUPVALUE('Date'[MonthDateSAP],'Date'[Date],FirstDayPrevMonth)
return
CALCULATE(SAPMonth)

Calculate(FirstDayPrevMonth), return the goode value (ex : 01.12.2020 00:00:00)

Calculate(SAPMonth) with EDATE(FirstDayOfMonth, 0) return me the same period

and Calculate(SAPMonth) with EDATE(FirstDayOfMonth, -1) return me blank value

Thanks to read until here, and sorry for my poor english and the long long text, but I tried to describe my trouble as best I can.

[PhilipTreacy]

Hi @Zorgonaute84 

Download sample PBIX file with data and DAX

If you create a proper Date column in your Sales and Warranty table this is very easy.  Here's the DAX for the new column

Date = DATEVALUE([Date Month Year])

You can then create measures for Sales This Month and Sales Last Month

Sales This Month = CALCULATE(SUM('Sales and Warranty'[Sales]), FILTER('Sales and Warranty', MONTH('Sales and Warranty'[Date]) = MONTH(TODAY())))

Sales Last Month = CALCULATE(SUM('Sales and Warranty'[Sales]), FILTER('Sales and Warranty', MONTH('Sales and Warranty'[Date]) = MONTH(TODAY())-1))

Which gives these results

Check my example file for this code.

Regards

Phil

[Zorgonaute84]

Thanks for your very quick response. The problem, I already tried to convert it into date, but it failed. The format of the [Date Month Year] look not standard. (and in French...)

[Zorgonaute84 · Accepted solution]

I succeed !!!

I can't convert into to date in PowerBI, but I did it into power query.

Then I used my original code (very similar to your except you  miss the article filter)

Sales Last 24 Mths (n-1) =

var Article = 'Sales and Warranty'[Ref.Article]

var DatePreviousMonth = EDATE('Sales and Warranty'[Date Month Year],-1)

return

CALCULATE(

SUM('Sales and Warranty'[Sales Last 24 Mths]),

FILTER('Sales and Warranty',

'Sales and Warranty'[Date Month Year] = DatePreviousMonth && 'Sales and Warranty'[Ref.Article]=Article))