Calculate Percent based on different FY measures

[ccrossmd]

I have a table that includes monthly expenditures for the current and the previous two fiscal years.
   
I am trying to create a matrix visual with Rows containing Division, Category, Fiscal Year and the columns include YTD_EXP and YTD_PCT. For the current fiscal year (2018), I want the total YTD amount to be divided by total fiscal year budget and for the two previous fiscal years  (2017 & 2016) I want the monthly YTD amount divided by total expenditures for the fiscal year. I appear to be close, as the FY2018 percent calculates correctly, but I can't seem to get both FY2017 and FY2016 to calculate properly. I can either get FY2017 correct or FY2016 correct, but not both at the same time. I am sure the problem relates to the evaluation context of the third if statement in the YTD_PCT measure below.

I am a novice with DAX/Power BI so I don't guarentee my formulas are the most efficient. Any suggestions as to how make both previous fiscal year percents calculate properly would be greatly appreciated.

The formuala for the YTD_PCT measure is:
YTD_PCT := If(MAX(CalendarDates[Date]) <= [Max Month],
    IF(VALUE(MAX(qryCurrBudget[fiscal_year])) = [Current FY],   Divide([YTD_EXP],  [TotalCurrBudget]),
        IF(VALUE(MAX(qryExpActuals[fiscal_year]))-1 = [Current FY]-1,  Divide([YTD_EXP],  CALCULATE(SUM(qryExpActuals[Exp_Amt]), DATESBETWEEN(CalendarDates[Date],MAX(FiscalYears[FY_Start_Date])-365,MAX(FiscalYears[FY_End_Date])-365))),
            IF(VALUE(MAX(qryExpActuals[fiscal_year]))-2 = [Current FY]-2, Divide([YTD_EXP], CALCULATE(SUM(qryExpActuals[Exp_Amt]), DATESBETWEEN(CalendarDates[Date],MAX(FiscalYears[FY_Start_Date])-720,MAX(FiscalYears[FY_End_Date])-720)))
            )
        )
    ),                   
    BLANK()
)

Other Measures:
Current_FY :=Max(CalendarDates[FiscalYear])
Max Month := EOMonth(MAX(qryExpActuals[MTH]),0)
TotalPDFBudget :=Sum(qryCurrBudget[budget_amt])
YTD_PDF := If(MAX(CalendarDates[Date] <= [Max Month],
    Calculate(TOTALYTD(SUM('qryExpActuals'[Exp_Amt]), CalendarDates[Date], FIlter(ALL(CalendarDates, CalendarDates[Date] <= MAX(CalendarDates[Date])), "09-30"))
    )

Data Model:

Divisions: Division, Division Name

Categories: Category, Description

CaendarDates: Date, FiscalYear, FiscalMonth, MonthText

FiscalYears: FiscalYear, FY_Start_date, FY_End_Date

qryExpActuals: Division, Category, MTH, fiscal_year, Exp_amt

qryCurrBudget: Division, Category, Mth, fiscal_year, budget_amt

 Download sample pbix file

[Greg_Deckler]

Can you include sample/example data and expected results? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[ccrossmd]

Greg,

Thank you for your response. This is my first post to any forum like this so this is all new to me. I've read your post and did try to include in my original post as much as I could about the structure of my workbook, but did not include any data or an image of my desired visualization. Is there a way to upload my smaple pbix file? If not, I will copy and paste the necessary data from the tables in my Power BI workbook.

Charlie

[ccrossmd]

Greg, 

I recently added a link to my original messasge so responders can download the sample pbix file and see the model, data and visualization. If you look at the visiualiztion you will see it is a matrix with a couple of levels of drill in on the rows. If drilled into the lowest level, which is how should appear when you download the pbix, you will see three fiscal years that display monthly YTD amounts and percent of total. The rows for 2016 and 2018 percentages are calculated properly. The 2017 row, however, is incorrect and appears to be using the same total as 2016 so I think the issue is with the third "if" statement in the YTD_PCT measure, but I certainly cannot figure it out. I have been working this for a couple of weeks (PT) and would greatly appreciate any assistance.

Charlie

[v-huizhn-msft]

Hi @ccrossmd,

Why the value of 2017 is uncorrect? The percentage is larger than 100%?

Thanks,
Angelia

[ccrossmd]

Angelia,

For 2016 and 2017 the final YTD_PCT amount should be 100%. Each line represents a monthly cumulative YTD amount for a fiscal year. The percent needs to reflect the monthly YTD amount divided by the total amount for that fiscal year. Looking at the matrix in the sample pbix file, the Aug YTD_EXP for 2017 for Div1, C1 = 642,124 and this amount should be divided by the total for 2017 771,734, which is the amount in the SEP YTD_EXP column. Therefore, the YTD_PCT for Aug 2017 for Div1, C1 should be 83.2% (642,124 / 771,734) and not the 78.4% as reflected in the matrix. The 78.4% represents the Aug 2017 YTD_EXP of 642,124 divided by the total for 2016 which is 984,604.

What makes this complicated, to me atleast, is that the percent for 2016 & 2017 needs to be calculated on the total expenditures for each fiscal year while 2018 is calcualted on total annual budget.

Any insight on how to change the YTD_PCT measure's formula would be greatly appreciated.

[v-huizhn-msft]

Hi @ccrossmd,

You can choose your pbix file as attachement file by clicking the option highlighted in yellow background.

Thanks，
Angelia

[ccrossmd]

Thanks Angelia,

Unfortunately, I do not see an atachements box at the bottom of my new message screen.

Charlie