Measure to show the name of the last month with data.

[franorio]

Hi everybody, 

I have some reports, all of them with a slicer of months (Calendar table Jan to Dic) 

For example for year 2017 I have data from Jan to Sept (closed month), If somebody clicks on Nov, Dic, there is no data for year 2017. But they appear because I'm using them to calculate a last estiamte by the end of the year. 

What I would like to do, is to add a string of text near to the Title of the report, indicating wich is the last month with data.

So every user can see in just one look, that the last update of new data was by September. 

Any ideas?

Tahnks & Regards!

[sdjensen · Accepted solution]

As I read you request you are asking for two things.... either you want a measure that returns the last month for which you have data available or you want the last date for data refresh which can be 2 different months.

I will in my reply assume that you mean the last month for which you have data.

You could try to create the following measure in your .pbix file and e.g use it with a card visual.

MaxMonthWithDataAllData = 
VAR MaxMonth = FORMAT( CALCULATE( MAX( FactTable[DateColomnInFactTable] ), ALL( FactTable ) ), "MMM" )
VAR MaxYear = FORMAT( CALCULATE( MAX( FactTable[DateColumnInFactTable] ); ALL( FactTable) ); "YYYY" )

RETURN
"The last month with data is " & MaxMonth & " " & MaxYear

The MAX function will return the maximum date in the column you specify.

The CALCULATE(, ALL( FactTable) will make sure to always return the maximum date disregarding any filters you have set to filter the table.

FORMAT is used to either return the month name or year.

[franorio]

Thanks a lot @sdjensen it totally worked!!

Best regards!