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")
)
