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

     

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper 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))