cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shelley
Continued Contributor
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])))

 

temp.PNG

2 REPLIES 2
Shelley
Continued Contributor
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?

temp2.PNG

 

 

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

 

Shelley
Continued Contributor
Continued Contributor

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
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors