Forum Discussion

Zorgonaute84's avatar
Zorgonaute84
Regular Visitor
5 years ago
Solved

Issues with Lookupvalue and EDATE return blank

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.

 

Need Help
  • Zorgonaute84's avatar
    Zorgonaute84
    5 years ago

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

     

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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's avatar
      Zorgonaute84
      Regular Visitor
      5 years ago

      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's avatar
        Zorgonaute84
        Regular Visitor
        5 years ago

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