Forum Discussion

ccrossmd's avatar
ccrossmd
Frequent Visitor
8 years ago

Calculate Percent based on different FY measures

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

Need Help

7 Replies

    • ccrossmd's avatar
      ccrossmd
      Frequent Visitor
      8 years ago

      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

       

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi ccrossmd,

         

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

         

        Thanks，
        Angelia