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

Financial/Reporting Month End different dates each month

Hi

My financial month ends are not calendar date based and they differ each month. I am encountering problems comparing months and years where
1) the Reporting/Accounting month is not calendar based (1st to last day) and
2) the current year month does not start on the same date as the prior year (eg 22 June, 25 July is the financial/reporting month end date so the TD should start on 23 June for July and 26 July for August... ; eg 21 June, 23 July PY vs 22 June, 25 July CY)


See below the table used for Reporting Periods, DIM_AccountingPeriod.

 

However this wont allow me to mark it as date table so that I can use Time Intelligence functions such as Year to Date (YTD). Month to Date (MTD) etc


dim acc period.png


then there is a DIM_Time table which can be used for Time Intelligence since it can be marked as date table. It is a proper date table.

 

dim time1.png

 

 

I would like to know if Time Intelligence functions will give me the correct MTD, YTD for each month based on these different month start dates. And if the above tables wont, what could be done to achieve that.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Assuming you have FY Month Start Date, FY Year Start Date , Based on that add follwing columns

Also, you can have FY year, in YYYY format , based on start or end date of year

 

new columns

Month Rank = RANKX(all('Date'),'Date'[FY Month Start date],,ASC,Dense)

Year Rank = RANKX(all('Date'),'Date'[FY Year Start date],,ASC,Dense)

Day of Year =datediff([FY Year Start date] , [Date],Day) +1

Day of Month=datediff([FY Month Start date] , [Date],Day) +1

 

measures

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

or

This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

 

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[FY Year]=max('Date'[FY Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[FY Year]=max('Date'[FY Year])-1))

 

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

You can also take a look at the following blog about use the date function to achieve YTD, MTD calculation without using time intelligence functions:

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@Anonymous , Assuming you have FY Month Start Date, FY Year Start Date , Based on that add follwing columns

Also, you can have FY year, in YYYY format , based on start or end date of year

 

new columns

Month Rank = RANKX(all('Date'),'Date'[FY Month Start date],,ASC,Dense)

Year Rank = RANKX(all('Date'),'Date'[FY Year Start date],,ASC,Dense)

Day of Year =datediff([FY Year Start date] , [Date],Day) +1

Day of Month=datediff([FY Month Start date] , [Date],Day) +1

 

measures

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

or

This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

 

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[FY Year]=max('Date'[FY Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[FY Year]=max('Date'[FY Year])-1))

 

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Anonymous
Not applicable

thank you @amitchandak let me test it

I think I am running into another potential problem re my date table. there are two systems in the DW. defined as such in the date table and fact table. initially i filtered out the one system from the date table to have unique date column, but I will need to report off two systems.. 

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.