Forum Discussion
Visualizing Cumulative Expressions by Fiscal Month
I solved one of the problems, the sorting of the fiscal months, by doing the solution here: https://community.powerbi.com/t5/Desktop/Display-graph-order-for-the-Financial-year-which-is-different-to/m-p/104064#M43576
However, I still cannot get this to calculate by Max Year or Max Year-1. What am I doing wrong? I am trying to do this because Power BI only allows one expression on a line chart. I want to show cumulative margin for this year, last year and plan, so I am trying to calculate years individually.
How can I calculate for just these years?
I'd like to attach the pbix file for you to review, but it appears as though I cannot attach it here.
Okay, I figured out my expressions - I wasn't making good use of my Max fiscal Yr Flag and Max Fiscal Yr-1 Flag, duh! Here are the new formulas:
Max Fiscal Year GM $ =
CALCULATE ('Field Dashboard GM Data'[Total Actual GM $], FILTER(ALL(Daily_Calendar),
Daily_Calendar[Max FY Flag] = 1 &&
Daily_Calendar[Fiscal_Year] = MAX('Field Dashboard GM Data'[Fiscal Year]) &&
Daily_Calendar[Fiscal_Month] <= MAX('Field Dashboard GM Data'[Fiscal Month])))
Max Fiscal Year GM Plan =
CALCULATE ('Field Dashboard GM Data'[Total AOP GM $], FILTER(ALL(Daily_Calendar),
Daily_Calendar[Max FY Flag] = 1 &&
Daily_Calendar[Fiscal_Year] = MAX('Field Dashboard GM Data'[Fiscal Year]) &&
Daily_Calendar[Fiscal_Month] <= MAX('Field Dashboard GM Data'[Fiscal Month])))
Max Fiscal Year-1 GM $ =
CALCULATE ('Field Dashboard GM Data'[Total Actual GM $], FILTER(ALL(Daily_Calendar),
Daily_Calendar[Max FY-1 Flag] = 1 &&
Daily_Calendar[Fiscal_Year] = MAX('Field Dashboard GM Data'[Fiscal Year]) &&
Daily_Calendar[Fiscal_Month] <= MAX('Field Dashboard GM Data'[Fiscal Month])))
Years are calculating now as expected, but what is happening now, is that the Max FIscal Year-1 data will not show in the chart!