Custom Time Intelligence Function

[Aahil10]

Hello,

I've managed to use the TotalMTD function to calulate variance on a monthly level - However we use production month which tends to be different for celandar month. So for example, whilst May ends on May 31, the production month May ends On June 5 as seen below:

My question is, is there anyway for me to filter the MTD meausre or use a different method to filter my results based on production month - Can this be done by creating a custom date table where the first 5 days of June fall under Month 5?

Or is there a simpler method to this?

[amitchandak]

@Aahil10 , You can create month start and end date like this for your the custom month in your date table

Month Start Date =  eomonth([date]-5, -1) +5

Month End Date =  eomonth([date]-5, 0) +4

Create a rank column too

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

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
Month Day = DATEDIFF('Date'[Month Start Date],'Date'[Date],Day)+1

Try measures like

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))

MTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [Month Day] <=max([Month Day])))
LMTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [Month Day] <=max([Month Day])))

[Aahil10]

Hey @amitchandak,

Thanks for the quick reply!

I created a date table from scratch to try this and got the following results:

I see the logic and it could potentially solve the problem. However, I'm wondering if the offset for every month is different, will it still work? Because it iterates the entire table with the query you rpovided.

For example, Jan Prod Month is Jan 1- Jan 30, so only one day is excluded. Whereas in my first example, May overflows into June by 5 days.

Basically, each month is different and I'm wondering if this solution can account for that?

Thank you!

[v-rzhou-msft]

Hi @Aahil10 

If your have different logic in each month, you can try switch function to build calculated columns.

Month Start Date = 
VAR _Minus =
    SWITCH (
        'Calendar'[Month],
        1, -1,
        2, 0,
        3, 0,
        4, 0,
        5, 0,
        6, 5,
        7, 0,
        8, 0,
        9, 0,
        10, 0,
        11, 0,
        0
    )
VAR _Add =
    SWITCH (
        MONTH(EOMONTH ( 'Calendar'[Date] - _Minus, -1 )+1),
        1, 1,
        2, 0,
        3, 1,
        4, 1,
        5, 1,
        6, 6,
        7, 1,
        8, 1,
        9, 1,
        10, 1,
        11, 1,
        1
    )
VAR _Result =
    EOMONTH ( 'Calendar'[Date] - _Minus, -1 ) +_Add
RETURN
_Result

Month End Date = 
VAR _Minus =
    SWITCH (
        'Calendar'[Month],
        1, -1,
        2, 0,
        3, 0,
        4, 0,
        5, 0,
        6, 5,
        7, 0,
        8, 0,
        9, 0,
        10, 0,
        11, 0,
        0
    )
VAR _Add =
    SWITCH (
        MONTH ( EOMONTH ( 'Calendar'[Date] - _Minus, 0 ) ),
        1, -1,
        2, 0,
        3, 0,
        4, 0,
        5, 5,
        6, 0,
        7, 0,
        8, 0,
        9, 0,
        10, 0,
        11, 0,
        0
    )
VAR _Result =
    EOMONTH ( 'Calendar'[Date] - _Minus, 0 ) + _Add
RETURN
    _Result

Result is as below.

Jan Prod Month is Jan 1- Jan 30,

May overflows into June by 5 days

You can change the number in switch by your requirement.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Aahil10]

Hi @v-rzhou-msft ,

Thanks for your detailed response - really appreciate it! I think this may actually work, however, 

I ran into an error trying to incorporate your formula:

Not sure how to resolve this. Am I doing something wrong? 

Also,

Would you know whether the MTD Function will work within the new dates that we have set in the above table, or whether I have to create new formulas to get a month to date total?

Thanks again!

[v-rzhou-msft · Accepted solution]

Hi @Aahil10 

In your screenshot, you build custom columns in Power Query Editor(M query). I build calculated column in Report view (Dax). Try to use my dax code to build calculated column.

For reference: Create calculated columns in Power BI Desktop

If you want to calcualte MTD for each "Month", you can build a Fiscal Month column.

Fiscal Month = RANKX(FILTER('Calendar','Calendar'[Year] = EARLIER('Calendar'[Year])),'Calendar'[Month Start Date],,ASC,Dense)

Ex: Jan Prod Month is Jan 1- Jan 30 is Month 1, 

May 1 - June 5 is Month 5.

Then you can calculate MTD by Fiscal Month for each Fiscal Month.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.