Forum Discussion
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.
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
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(:
- Found the Max Data of the table
- Grouped the table by Products, and added a column for the Max Date of that product
- 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
- 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]
- 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) )
- That will produce a list of all the days between those two days for each product. Expand that list out
- End table looks like this:
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)
pbix is here:
https://1drv.ms/u/s!Amqd8ArUSwDSz0IOCvktg-arAeCk
6 Replies
Who would have thought that it is as simple as this.
Thanks for sharing!
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
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(:
- Found the Max Data of the table
- Grouped the table by Products, and added a column for the Max Date of that product
- 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
- 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]
- 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) )
- That will produce a list of all the days between those two days for each product. Expand that list out
- End table looks like this:
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)
pbix is here:
https://1drv.ms/u/s!Amqd8ArUSwDSz0IOCvktg-arAeCk
