cancel
Showing results for
Did you mean:  Continued Contributor

## Visualizing Cumulative Expressions by Fiscal Month

I am trying to show several cumulative expressions over fiscal months. I'm probably missing something simple, but I cannot figure it out, so I really appreciate any help. I have flags for Maximum fiscal year and maximum fiscal year -1 in my calendar table and they appear to be working.

Max FY Flag = IF(Daily_Calendar[Fiscal_Year] = [Max Data FY], 1, 0)

Max FY-1 Flag = IF(Daily_Calendar[Fiscal_Year] = [Max Data FY] - 1, 1, 0)

The cumulative expressions seem to be calculating properly, but I'm not getting the exact results I want. I want cumulative values for the Max fiscal year - both actuals and plan, and then I want cumulative actuals for the Last fiscal year. My expressions are calculating two years for the Max year, and the Max year for the values I want for Max year -1, so something's probably wrong with my filtering. Then, on top of it, the cumulative graph by month starts with September and our fiscal year should start with October. It is also not sloping as expected but maybe that's because something is wrong with  my expressions.

Max Fiscal Year GM \$ =
CALCULATE (SUM('Field Dashboard GM Data'[Actual GM \$]), FILTER (ALL(Daily_Calendar),
Daily_Calendar[Max FY Flag] = 1
&&
Daily_Calendar[Fiscal_Month] <= MAX(Daily_Calendar[Fiscal_Month])))

Max Fiscal Year GM AOP =
CALCULATE (SUM('Field Dashboard GM Data'[AOP GM\$]), FILTER (ALLSELECTED(Daily_Calendar),
Daily_Calendar[Fiscal_Year] <= MAX(Daily_Calendar[Fiscal_Year])
&&
Daily_Calendar[Fiscal_Month] <= MAX(Daily_Calendar[Fiscal_Month])))

Max Fiscal Year-1 GM \$ =
CALCULATE (SUM('Field Dashboard GM Data'[Actual GM \$]), FILTER (ALLSELECTED(Daily_Calendar),
Daily_Calendar[Fiscal_Year] <= (MAX(Daily_Calendar[Fiscal_Year]) - 1)
&&
Daily_Calendar[Fiscal_Month] <= MAX(Daily_Calendar[Fiscal_Month]))) 2 REPLIES 2  Continued Contributor

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-differe...

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.  Continued Contributor 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! Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates. #### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!  