cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create Measure for finding most recent value

I'm importing CRM data where a dynamic value is changed over time. This value represents the quality of a lead source as leads from that source moves down the sales funnel. I need to create a measure that identifies the most recent value, so that I can use that to multiply with new leads coming in, in order to project the sales coming from those leads. Below is an example of how the data table looks like.

 

 

Date     Product    Quality

2/22      A              0.05

2/21      B              0.05

2/17      C              0.05

2/16      A              0.03

2/13      B              0.07

2/10      C              0.08

 

 

 

 
 
 
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Create Measure for finding most recent value

This will bring in the last quality value depeding on what product is being used:

Measure = 
CALCULATE( SUM ( CRM[Quality] ), LASTDATE(CRM[Date ] ))

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Create Measure for finding most recent value

I decided to go the Power query route for this one. I tried using just DAX and just wasnt working for me. So here's what I did in Power Query ( the pbix is attached below so you can step through the applied steps(:

 

  1. Found the Max Data of the table
  2. Grouped the table by Products, and added a column for the Max Date of that product
  3. Group By.png
  4. Expand that data out, so have the date of the row, the max date of the entire table and the max date for that product
  5. Added a custom column to figure what End date to use for each row
if 
[#"Date "] < [Max Product Date] 
then 
Date.AddDays( [Max Product Date], -1) 
else 
[Max of Table]
  1. Removed some misc columns and added a new column to get a list of dates from the date of the actual row to end date as defined by # 5 above
List.Dates(
[#"Date "],
Duration.Days( [End Date] - [#"Date "]) +1 ,
#duration(1,0,0,0)
)
  1. That will produce a list of all the days between those two days for each product. Expand that list out
  2. End table looks like this:
  3. End PQ table.png

 

What this is doing is making sure every day is accounted for and will  "bring in" the last value.  

 

Load that in, and then write the following measure:

 Total Quantity = 
    IF( 
        ISFILTERED('CRM (3)'[Dates]),
            SUM ( 'CRM (3)'[Quality] ),
            "Please Select a Date"
    )

Now, dont want to sum across days since that would not make sense ( and now that i think about it, probbaly could use lastdate, but this works too) 

Final Matrix.png

pbix is here:

https://1drv.ms/u/s!Amqd8ArUSwDSz0IOCvktg-arAeCk

 

View solution in original post

4 REPLIES 4
Highlighted
Anonymous
Not applicable

Re: Create Measure for finding most recent value

This will bring in the last quality value depeding on what product is being used:

Measure = 
CALCULATE( SUM ( CRM[Quality] ), LASTDATE(CRM[Date ] ))

View solution in original post

Highlighted
Frequent Visitor

Re: Create Measure for finding most recent value

Actually it didn't quite solve my problem.

 

With your suggestion I was able to report the last date a quality index was updated. So 2/22 Product A was 0.05 and Product B/C is missing so that returns a blank value.

 

Since I don't get an updated quality value every day I was looking for a measure that reports the most recent value for each product the last time it was reported, so that in a date interval 2/1 - 2/22 I would get this result:

 

Product     Quality

A                0.05

B                0.05

C                0.05

 

 

Highlighted
Anonymous
Not applicable

Re: Create Measure for finding most recent value

I decided to go the Power query route for this one. I tried using just DAX and just wasnt working for me. So here's what I did in Power Query ( the pbix is attached below so you can step through the applied steps(:

 

  1. Found the Max Data of the table
  2. Grouped the table by Products, and added a column for the Max Date of that product
  3. Group By.png
  4. Expand that data out, so have the date of the row, the max date of the entire table and the max date for that product
  5. Added a custom column to figure what End date to use for each row
if 
[#"Date "] < [Max Product Date] 
then 
Date.AddDays( [Max Product Date], -1) 
else 
[Max of Table]
  1. Removed some misc columns and added a new column to get a list of dates from the date of the actual row to end date as defined by # 5 above
List.Dates(
[#"Date "],
Duration.Days( [End Date] - [#"Date "]) +1 ,
#duration(1,0,0,0)
)
  1. That will produce a list of all the days between those two days for each product. Expand that list out
  2. End table looks like this:
  3. End PQ table.png

 

What this is doing is making sure every day is accounted for and will  "bring in" the last value.  

 

Load that in, and then write the following measure:

 Total Quantity = 
    IF( 
        ISFILTERED('CRM (3)'[Dates]),
            SUM ( 'CRM (3)'[Quality] ),
            "Please Select a Date"
    )

Now, dont want to sum across days since that would not make sense ( and now that i think about it, probbaly could use lastdate, but this works too) 

Final Matrix.png

pbix is here:

https://1drv.ms/u/s!Amqd8ArUSwDSz0IOCvktg-arAeCk

 

View solution in original post

Highlighted
Frequent Visitor

Re: Create Measure for finding most recent value

That did it, thanks a bunch!

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors