Show only the last date when an event happened

[Anonymous]

Hello community, I am trying to create a DAX formula (Calculated column) that will bring the days difference between the last event and today. Here is a screenshot of the matrix and my DAX formula.

Date Difference Taken Date/Today =

VAR last_PR =

    CALCULATE (

        MAX ( Readings[taken_date]),

            'Readings'[value] = "Yes"

               && ( 'Readings'[description] = "Plunger Maintenance Plunger Replaced" )

        )

RETURN

    DATEDIFF ( last_PR, TODAY (), DAY )

The formula works if there is only one replacement per row, but if there are more than one it will add all days from each event till today.

 This is fine, but this is showing the totals for all

I would like to have the difference from last event even there are multiple throughout the year. Any suggestions? Thank you

[Anonymous]

Hi @Anonymous ,

Please update the formula of the calculated column [Date Difference Taken Date/Today] as below, the part with red font is updated ones...

Date Difference Taken Date/Today =
VAR last_PR =
    CALCULATE (
        MAX ( 'Readings'[taken_date] ),
        FILTER (
            'Readings',
            'Readings'[event] = EARLIER ( 'Readings'[event] )
                && 'Readings'[value] = "Yes"
                && ( 'Readings'[description] = "Plunger Maintenance Plunger Replaced" )
        )
    )
RETURN
    DATEDIFF ( last_PR, TODAY (), DAY ) 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous · Accepted solution]

@Anonymous 

Hello Ying, I think I fixed the formula. I used the filter function as suggested but with the measure and VALUES function.

Date Difference Taken Date/Today test =

VAR last_PR =

    CALCULATE (

        MAX ( Readings[taken_date] ),

        FILTER ( VALUES ( Readings[taken_date] ), [Plungers raw] <> 0 )

    )

RETURN

    DATEDIFF ( last_PR, TODAY (), DAY )

And the results are showcased below. Difference between formulas! Thank you for your suggestions!

[Anonymous]

Hi @Anonymous,

It's glad to hear that your problem has been resolved. And thanks for sharing your solution here. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours.  Thank you.

Best Regards

[Anonymous]

Hi @Anonymous ,

the post helped but there is no option to say it helped (it should be), and if I accept it as a solution it would be wrong becasue it is not the "solution". Yes, I can post it here that your suggestion helped, so others can see, but the solution is a bit different.

[Anonymous]

@Anonymous 

I tried the improved formula

Date Difference Taken Date/Today =

VAR last_PR =

    CALCULATE (

        MAX ( Readings[taken_date]),

         FILTER(

             Readings,

             Readings[taken_date]=EARLIER(Readings[taken_date])

            &&'Readings'[value] = "Yes"

            && ( 'Readings'[description] = "Plunger Maintenance Plunger Replaced" )

        )

    )

RETURN

    DATEDIFF ( last_PR, TODAY (), DAY ),

but it gives wrong results even for one event a year. To mention that the "1" values in the matrix are a result of a measure:

Plungers raw =

CALCULATE (

    COUNTROWS ( 'Readings' ),

    'Readings'[value] = "Yes"

        && 'Readings'[description] = "Plunger Maintenance Plunger Replaced"

)

Maybe if we can filter that measure to show only the last value.

results with new formula for one event (is wrong). Is 477 instead of around 80.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
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. Screenshots of the expected outcome are ok.

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