Forum Discussion

Aahil10's avatar
Aahil10
Frequent Visitor
5 years ago
Solved

Custom Time Intelligence Function

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?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Aahil10
      Frequent Visitor
      5 years ago

      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!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.