cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Responsive Resident
Responsive Resident

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

 

temp.PNG

2 REPLIES 2
Highlighted
Responsive Resident
Responsive Resident

Re: 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-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?

temp2.PNG

 

 

I'd like to attach the pbix file for you to review, but it appears as though I cannot attach it here.

 

Highlighted
Responsive Resident
Responsive Resident

Re: Visualizing Cumulative Expressions by Fiscal Month

Capture3.PNG

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!


Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors