How to use a value from a date field as my filter

[morganrosser]

I have data in 2 tables
analytic_metrics
assets

These tables are related using a serial number. 

The asset table houses a "last replaced date."

I'd like to run a report out of my analytic_metrics table that runs a sum of shots since the "last replaced date" in the asset table. 

Am I able to use this last replaced date value as my filter? 

Thanks

[v-binbinyu-msft · Accepted solution]

Hi @morganrosser ,

Please try below steps:

Create a measure with below dax formula:

Measure =
VAR cur_id =
    SELECTEDVALUE ( Analytic_metrics[sim_id] )
VAR _date =
    CALCULATE (
        MAX ( Asset[Date_Replaced_hitting_Strip] ),
        Asset[SerialNumber] = cur_id
    )
VAR tmp =
    FILTER ( ALL ( Analytic_metrics ), [sim_id] = cur_id && [date] > _date )
RETURN
    IF ( ISBLANK ( _date ), 0, SUMX ( tmp, [shots] ) )

Add a table visual with Analytic_metrics[sim_id] and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[morganrosser]

Hi, @v-binbinyu-msft ,

Thanks for the reply. 

Apologies for the delay, I had some travel mixed in that made it tough to respond. 

I was able to use your dax formula to create the measure. But I uncovered an issue in field formatting in my tables. The sim_id is an integer while the sim ID is text/number. 

Should I create a measure that converts the sim_id to a number or do I build that into the provided dax formula? 

Thanks

[lbendlin]

yes, that is possible.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[morganrosser]

Hi, thanks for the response!

Sample Data

Table 1: analytic_metrics

sim_id	date	shots	location_id
20693482	10/20/2022	100	2
20693482	10/18/2022	250	2
70321005	10/20/2022	75	79
20693482	10/10/2022	50	2

Table 2: Asset

SerialNumber	Date_Replaced_hitting_Strip
20693482	10/17/2022

I have the above two tables where 

analytic.sim_id = Asset.SerialNumber

I want to create a table where I can show shots on sim_id/SerialNumber = 20693482 since the Date_Replaced_Hitting_Strip (10/17/2022). A table format would be perfect:

Filters

Sim_id = 20693482 (I'm fine hard coding this filter as we need)

analytic_metrics.Date = Asset.Date_Replaced_Hitting_Strip 

sim_id	sum of shots
20693482	350

Thanks!