Forum Discussion

Chtlim's avatar
Chtlim
Regular Visitor
5 years ago

MTD Calculation not working properly

I have a date table based on this dax formula.

 
Date = ADDCOLUMNS (CALENDAR (DATE(2016,1,1), DATE(2021,12,31))
 
I have another table of billings by business unit:
Actual Billings
 

I've created a visual (table) in which I would like to summarize the MTD Actual Billings by each Business unit.

 

When I use:

MTD Actual Billings = TOTALMTD(Sum('Cost'[JTD Actual Billings]),'Date'[Date].[Date]) - I get blank values.
 
When I use:
TOTALMTD(Sum('Cost'[JTD Actual Billings]),'JDE Das Report F0911 - Cost'[G/L Date]) -
I mostly get the right values, however some business units are showing the last month it has recorded values for. (ie. Business Unit 5057000 should be 0, but its showing -$11049 which is the sum for Sept 2020, there are no other transactions for that business unit afterwards. However, the total at the bottom is correct.) 
 

 

Can someone suggest how I can fix this or to calculate MTD?

 

Thanks in advance!

 

Chris

Need Help

1 Reply

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    To create a Date table, just click on New Table under Modelling

    Date = calendar(DATE(2016,1,1), DATE(2021,12,31))

    Build a relationship from the Date column of your Cost table to the Date column of the Date table.

    In the Date table, write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name column by the Month number column.  Create 2 slicers - one for Year and Month name.  Select a Year and a Month name.  Write these measures

    Total cost = sum('Cost'[JTD Actual Billings])

    Total cost MTD = calculate([Total cost],datesmtd('Date'[Date]))

    Hope this helps.