Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

Anonymous
Not applicable

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

5 REPLIES 5
Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

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

 

pbix download is not working anymore. Could you please provide a new link? thx!

Anonymous
Not applicable

That did it, thanks a bunch!

Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.