Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
franorio
Helper III
Helper III

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

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!

1 ACCEPTED SOLUTION
sdjensen
Solution Sage
Solution Sage

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.

/sdjensen

View solution in original post

2 REPLIES 2
sdjensen
Solution Sage
Solution Sage

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.

/sdjensen

Thanks a lot @sdjensen it totally worked!!

 

Best regards!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.