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
iqbal077
Frequent Visitor

Start of the month/ End of the month

 

My model has two inactive relatioships to the date table to two dates columns in the fact table. This is done to help with few of the measures that I have built in the model. The two dates columns are reading date and revenue date. Reading date aka recorded date  or report generated date.  The report is generated on a daily basis. On the otherhand, revenue date is the actual financial date where room sales and revenues are attributed to.  I am having diffIculties in calculating  revenues at the start of the month and end of the month for past and future months in the calendar. 

Spoiler
Screen shot of the fact tableScreen shot of the fact table

logic - if the reading date = startof the month ( eg: 1/1/2020 ), then calculate revenue for the respective months ( revenue date) in the calendar. All future revenue numbers will get updated as we get new batch of files. 

Example - on the 1/1/ 2020, the revenue recorded for March was 1 mn, as with time and we get new files, revenue for the month of March would probably be at 1.5mn on the 1st of Feb.    Essentialy, all future month's revenue position is a snapshot at a given start of the month. and all the past months will remain at their actualized numbers. 

 

This Year =
VAR SDate = STARTOFMONTH('Sales_Revenue Fact sheet'[Reading Date])
RETURN
CALCULATE( SUM('Sales_Revenue Fact sheet'[Rooms Revenue]),FILTER(ALL('Sales_Revenue Fact sheet'),
'Sales_Revenue Fact sheet'[Revenue Date]= MAX('Calendar'[Month]) &&
'Sales_Revenue Fact sheet'[Reading Date] = SDate))
//START OF THE MONTH//

 

MonthStart of the monthEnd of the monthChange 
Jan   
Feb   
Mar   
Apr   
May   
June   
July   
Aug   
Sep   
Oct   
Nov   
Dec   

 

Thanks in advance

Iqbal 

2 REPLIES 2
sturlaws
Resident Rockstar
Resident Rockstar

Hi @iqbal077 

 

do you still have issues with this?

 

Cheers,
Sturla

vivran22
Community Champion
Community Champion

Hello @iqbal077 ,

 

The requirement is not clear from your description. What is the logic of calculating revenue for March based on revenue of January?

 

Can you share a sample data file with expected results?

 

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

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.

Top Solution Authors