Month offset for company start and end date

Power Query M Code Showcase
1

Date.xlsx (49.4 KB)

I am seeking help in correcting a month offset formula for calculating fiscal months in my company. Currently, I’m using the following formula:

(Date.Year([Dates In Period]) - Date.Year([Current Date])) * 12

  • Date.Month([Dates In Period]) - (Date.Month([Current Date]))

The formula works well in most cases, but I’ve noticed an issue when the fiscal month ends on the 29th of July and starts on the 30th of July. During this transition, the month offset doesn’t show as 0 for the 30th of July until the calendar moves to the 1st of August.

Sample data:

  • Fiscal month end date: 29th July
  • Fiscal month start date: 30th July
  • Current date: 30th July

Expected outcome: The month offset should be 0 on 30th July.

Desired Outcome: I want the formula to correctly handle cases where the fiscal month ends and starts on consecutive days, like in the example above.

Any help in resolving this issue would be greatly appreciated!

Thank you in advance.

Shruti

2

Hello @shruti, and thank you for starting a conversation in the Community Forum. One of the standing requests of all folks who contribute to the Forum is to add a work-in-progress Power BI Desktop file, include an image of the tables and relationships, and describe and format any DAX in use. Please also include one or more images of your desired outcome as this contributes value to the conversation. It is also appreciated that special attention is paid to communicating the issue and desired results in plain language, as a reader is not familiar with your line of business.

3

image
image1412×646 49.8 KB

I have already uploaded the Excel file with the Date table.

My company’s fiscal month starts on 30/07/2023 and ends on 26/08/2023–

The offset month formula only provides 0 when the calendar month changes to August. after refreshing the data It was showing 0 for the month of whole July instead of -1 till 29th July.

Please can you help me with the offset formula

4

HI @shruti,
Did you do a search in the forum for not normal fiscal years? I kinda remember there is a similiar topic that might be able to help. Right now, i can’t find it.

thanks
Keith

5

Hi Keith I had searched the forum but was not able to see the correct calculation

6

may not be the correct calculation but it may give you an idea how to do it. If I remember correctly, likely @Melissa answer the question as she was the one that implemented the EDNA Date table (m coding) Make do a search on her name.

thanks
Keith

7

Change up your search in the forum, and even take the search out to Google, and specify the site at Enterprise DNA, as there are many topics, discussions, code samples, and patterns on Offsets. You may need also to look at the attached sample PBIX for it.

8

Hi @shruti,

The issue is that your comparing to the month and year for the current date, not the fiscal month and fiscal year belonging to the current date.

Just lookup the record, like so:

image
image773×209 7.7 KB

And use these values in your calculation

image
image1484×481 35.6 KB

Here’s the updated script

let
    TodaysRec = AddDatesInPeriod{[Date = /* #date(2023, 7, 31) */ DateTime.Date(DateTime.FixedLocalNow()) ]},
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ListDates = Table.AddColumn(Source, "Date", each List.Transform( { Number.From ( [StartDate] ) ..Number.From ( [EndDate]) }, each Date.From (_) )),
    ExpandDates = Table.ExpandListColumn(ListDates, "Date"),
    AddMonthNum = Table.AddColumn(ExpandDates, "Month Number", each Date.Month (Date.AddDays([StartDate],Number.RoundUp (Duration.Days ([EndDate] - [StartDate] )/2)))),
    AddMonthName = Table.AddColumn(AddMonthNum, "Month", each Date.MonthName (Date.AddDays([StartDate],Number.RoundUp (Duration.Days ([EndDate] - [StartDate] )/2)))),
    AddDatesInPeriod = Table.AddColumn(AddMonthName, "Dates In Period", each Date.From( Number.ToText([FiscalYear]) &" "& [Month])),
    AddMonthOffset = Table.AddColumn(AddDatesInPeriod, "Month Offset", each (Date.Year([Dates In Period]) - TodaysRec[FiscalYear])*12 
        + (Date.Month([Dates In Period]) - TodaysRec[FiscalPeriod] )),
    ChType = Table.TransformColumnTypes( AddMonthOffset, {{"StartDate", type datetime}, {"EndDate", type datetime}, {"FiscalPeriod", Int64.Type}, {"FiscalYear", Int64.Type}, {"Date", type date}, {"Month Number", Int64.Type}, {"Month", type text}, {"Dates In Period", type date}, {"Month Offset", Int64.Type}})
in
    ChType

I hope this is helpful