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. 

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.