Hi,
- I have two tables (Date and Exec_CivilMonthStats) and a common date column (ReportMonthEnd) in both of them.
- I want to have ReportMonthEnd column in the Date table to used as a common slicer which I am not able to do or MMMM-YYYY to drive my tables.
- To give a more context why I am doing this as in Exec_CivilMonthStats table I have a calculated field for "Last twelvemonths data" with below formula
Finalized LTM =
VAR CurrentDate = MAX('Date'[ReportMonthEnd])
Var PreviousDate = DATE(YEAR(CurrentDate)-1,MONTH(CurrentDate), DAY(CurrentDate))
Var Result =
CALCULATE(
SUM(Exec_CivilMonthStats[Finalized Flag]),
FILTER(
Exec_CivilMonthStats, Exec_CivilMonthStats[ReportMonthEnd] >=PreviousDate && Exec_CivilMonthStats[ReportMonthEnd]<=CurrentDate
)
)
Return
Result
If I use ReportMonthEnd from Exec_CivilMonthStats table, then it narrows down my result to that particular month for "Finalized LTM" instead of showing last twelve months for "Finalized LTM".
Please refer to Page 1 and Page 2 of the attached workbook where Page 2 shows the unwanted change to Finalized LTM.
https://drive.google.com/open?id=1QmPgG6CJEQzxE9vYtjqR_s1eeVdAMbd-