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
Anonymous
Not applicable

Restrict latest date depending on calendar day of the month

Hi All,

 

I have a daily stream of "market value" data.  I can easily set up a report to display data as of the latest date or as of the latest month end date that is within the last 1 calendar months using relative date filtering.  

 

However my unique need is to only show the latest month end date if the current day of the month is at least the 8th calendar day or later.  If it isn't then the report should display as of the second latest month end.

 

For example:

  • If today is 9/7/2018, it's not the 8th calendar day, thus I would only want to show data as of 7/31/2018
  • Tomorrow will by 9/8/2018, it will be at least the 8th calendar day of the month, and we should see data as of 8/31/2018

My thought is to create a calculated column in my Dates table, with the logic of If today is greater than 7, monthend date is most recent from last month, otherwise monthend date is most recent from 2 months ago.

 

Any other easier ways to do it?  Possibly without creating a calculated column?

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

Hi ChrisIsWorking,

 

So your requirement is to represent the lastest month end or second latest month end based off your logic, right? This logic can't be recognized by visual automatically so you have to create a calculate column or a measure instead. In your senario, if you just want to represent the date value in some visual, a measure may be more appropriate for you. If you want to change your data model, I would recommend that you cant still create a calculate column.

 

Regards,

Jimmy Tao

Anonymous
Not applicable

Hi Jimmy, thanks for the response.

 

In addition to having the date in a visual, I also need the additional data in a table visual for example to be as of the latest month end or second latest month end.  So it's corresponding data to either month end.

 

I agree with having a calculated column.  From your perspective, the calculated column should be in the Dates table yes?  Or would it be in the table that contains the additional data that is being used in the table visuals?

 

Many thanks for your help!

 

 

 

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.