Latest Date when refreshedfor Day over Day

[Jpahl2033]

Hi,

I am trying to figure out a way when i refresh my data at the end of the day, the measure would pull the latest Day over Day measure. For example, if i refreshed my data at the end of today (June 16th) and i look at it later today or even tomorrow before the next refresh would happen, it would show the day over day % change for June 16th. If you need a value, you can use sales as a sample value. 

Let me know if it is possible. Thanks! 

[v-yanjiang-msft · Accepted solution]

Hi @Jpahl2033 ,

According to your description, I create a sample.

If you want to show the division of the previous day and the latest day, here's my solution.

Create a measure.

Day over Day =
VAR _Today =
    CALCULATE (
        SUM ( 'orders data weekly'[Sales] ),
        'orders data weekly'[OrderDate]
            = MAXX ( ALL ( 'orders data weekly' ), 'orders data weekly'[OrderDate] )
    )
VAR _Pre =
    CALCULATE (
        SUM ( 'orders data weekly'[Sales] ),
        'orders data weekly'[OrderDate]
            = MAXX ( ALL ( 'orders data weekly' ), 'orders data weekly'[OrderDate] ) - 1
    )
RETURN
    DIVIDE ( _Pre, _Today )

Get the result.

I attach the sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jpahl2033]

@Greg_Deckler

This is my current measure. do you know where i would put that?

Day over Day =

VAR

_prev = IF(

NOT( ISBLANK( [Total Sales]))

, CALCULATE([Total Sales]

, PREVIOUSDAY('orders data weekly'[OrderDate])))

RETURN

DIVIDE( ( [Total Sales] - _prev ),_prev)

[Greg_Deckler]

@Jpahl2033 In theory: PREVIOUSDAY(MAX('new query'[Last Refreshed]))

[Greg_Deckler]

@Jpahl2033 Often you just create a query for this that returns a single row and column that is the last refreshed time. DateTime.LocalNow(). Then you can use that in your DAX measures to calculate based upon the last refreshed date.