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

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. 

     

     

    • Aahil10's avatar
      Aahil10
      Frequent Visitor
      5 years ago

      Hi Anonymous ,

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.