Forum Discussion

Razorbx13's avatar
Razorbx13
Icon for Post Patron rankPost Patron
5 years ago
Solved

MTD Totals

Hello!  I have a P&L statement that allows users to go back in time to filter data based on a specific month end, then show MTD, YTD and Prior YTD totals as of that date.   As an example, I have a user select a filter of MONTH END, and they set it as "on or before" 9/30/2020.  Today is April 16th.  I have a join between my GL tables (MONTH END DATE) to my SQL Server Time table (CALENDAR DATE). 

 

Well the net totals of the DAX calculation below (1-9) are correct for each of the categories, but I am bringing in data from months other than 9/30/2020 in my MTD column which is representing months other than 9/30/2020.  If I filter for MONTH END "IS" 9/30/2020 everything is fine, but my YTD and PYTD are now off.  So how can I fix this?  The user could go back to say 11/30/2018 if they wanted.  And they filter on Month End, not a GL posting date.  So today they would set a filter of April 30, 2021.

 

BTW - I get the MTD to work if I hard code the Month (September) and Year (2020) into the FILTER below.   I also need that SWITCH command as well.

 

Thanks in advance.

 

 

MTD Actual = CALCULATE (    
    TOTALMTD (
    SWITCH ( [Row Selected Calc (SO)],
1,[Revenue (SO)],
2,[Cost of Sales (SO)],
3,[Gross Margin (SO)],
4,[Salary/Benefits (SO)],
5,[Other Expense (SO)],
6,[Net Income Before (SO)],
7,[Direct Allocations (SO)],
8,[Net Income After (SO)],
9,[Capital Expenditures (SO)] )
    ,'Time (GL Month End)'[Calendar Date]
    )   
     ,FILTER('GL Transactions (Statement of Operations)',[Account Type] = "ACTUAL")
    )
Need Help
Show and Tell
  • Razorbx13's avatar
    Razorbx13
    5 years ago

    I think I got it working using the calculation below.  Notice the filter.

     

    MTD Actual = CALCULATE (    
    TOTALMTD (
        SWITCH ( [Row Selected Calc (SO)],
    1,[Revenue (SO)],
    2,[Cost of Sales (SO)],
    3,[Gross Margin (SO)],
    4,[Salary/Benefits (SO)],
    5,[Other Expense (SO)],
    6,[Net Income Before (SO)],
    7,[Direct Allocations (SO)],
    8,[Net Income After (SO)],
    9,[Capital Expenditures (SO)] )
        ,'Time (GL Month End)'[Calendar Date]
        )   
         ,FILTER('GL Transactions (Statement of Operations)',[Month End] = MAX('Time (GL Month End)'[Month End]) && 'GL Transactions (Statement of Operations)'[Account Type]="ACTUAL")
        )

2 Replies

    • Razorbx13's avatar
      Razorbx13
      Icon for Post Patron rankPost Patron
      5 years ago

      I think I got it working using the calculation below.  Notice the filter.

       

      MTD Actual = CALCULATE (    
      TOTALMTD (
          SWITCH ( [Row Selected Calc (SO)],
      1,[Revenue (SO)],
      2,[Cost of Sales (SO)],
      3,[Gross Margin (SO)],
      4,[Salary/Benefits (SO)],
      5,[Other Expense (SO)],
      6,[Net Income Before (SO)],
      7,[Direct Allocations (SO)],
      8,[Net Income After (SO)],
      9,[Capital Expenditures (SO)] )
          ,'Time (GL Month End)'[Calendar Date]
          )   
           ,FILTER('GL Transactions (Statement of Operations)',[Month End] = MAX('Time (GL Month End)'[Month End]) && 'GL Transactions (Statement of Operations)'[Account Type]="ACTUAL")
          )