Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Lookup value for same day last month

Hi,

 

As a DAX beginner, I woul really appreciate some assitance from this community. Please allow me to explain

What I’m trying to do here is to visualizable success rate based on the number of successfull attempts vs the attempts.

  • Per day
  • Per person

For this I Have fairly easy model with a data table and a date/calendar table

 

Now the tricky bit:

 

What I would like to do is to compare the success rate of a person to the same rate a week before

 

Example

  • 10/1/20: Success rate for participant ID0-105 is= 72%
  • 09/1:20: (same day last month) the success rate for participant ID0-105 is = X

 

What I’m struggling with is to find a way to retrieve the information of the ‘same day last month’

 

I would like my dashboard ot behave like this

  • Select a date
  • See the Success rate for that date
  • See the Success rate for the same day last month
  • Compare both to check if it’s better or worse through conditional formatting (easy)

So far I’ve been able to calculate the last month date with a “edate fucntion” but I struggle to find the right logic to also get the values of the selected date -1

 

if you want tot take a look at my pbix file: https://we.tl/t-R7InaeWHWE

 

Is there anyone here available to help me take my Power BI skills to the next level?

 

Many thanks

 

 

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

    https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

     

    question is what you want to do when the current month is 31 days and the previous month is 30 days??

     

    although you can use EDATE DAX  function to get the previous month, same date.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      i did use edate function to get the 'same day last month' but now I want to use this to lookup to date value and retrieve the attemps and success rates this day, how can I do that?