Indication of the latest DATE that has value (Data is as of: ______)

[nhol]

Hi,

I guess this was asked several times but I still wasn't able to find a solution to my query.

I have a set of data that is being updated randomly and not on a daily basis (otherwise it was easy).

I need to let my users know what is the latest DATE that dataset is referring to.

For example if they are pulling the data on January 5, 2018 they should be able to know that the data is up-to-date as of 04/01/2018, or if they pull the data a couple of days later on January 10, 2018, it should be noted that the data is as of 07/01/2018.

I tried to build a function using MAX but was not able to nail this.

Any help would be highly appreciated.

Thanks,

NH

[v-jiascu-msft · Accepted solution]

Hi @nhol,

I have two approaches that are all measures. Please try them out.

Measure 1 =
CALCULATE (
    LASTDATE ( 'Table1'[Date] ),
    ISBLANK ( 'Table1'[Sales] ) = FALSE ()
)

Measure 2 =
LASTNONBLANK ( 'Table1'[Date], CALCULATE ( SUM ( 'Table1'[Sales] ) ) )

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @nhol,

I have two approaches that are all measures. Please try them out.

Measure 1 =
CALCULATE (
    LASTDATE ( 'Table1'[Date] ),
    ISBLANK ( 'Table1'[Sales] ) = FALSE ()
)

Measure 2 =
LASTNONBLANK ( 'Table1'[Date], CALCULATE ( SUM ( 'Table1'[Sales] ) ) )

Best Regards,

Dale

[nhol]

Thanks! Both of them work. 

What would it be if I want to use a column and not a measure?

NH

[v-jiascu-msft · Accepted solution]

Hi NH,

Try this formula as a calculated column please.

Column =
CALCULATE (
    LASTDATE ( 'Table1'[Date] ),
    FILTER ( 'Table1', ISBLANK ( 'Table1'[Sales] ) = FALSE () )
)

Best Regards,

Dale

[nhol]

Thank you Dale!