Created by Miki Yanagi, last modified on Nov 09, 2021

This is a fact table that includes measurable outcomes, such as actuals or budget data.

Actual

Field Name CalculationNotes

% Actual Net Change (YTD)

DIVIDE( [Actual Net Change (YTD)], [Actual (YTD FY - 1)], 0)


$ Actual (Non-Current)

$ Actual (Non-Current) 
CALCULATE (
    [Actual (YTD)],
    FILTER (
        'Account',
            Account[Account Number] IN { "13160", "13168", "13450", "13451", "13452", "13267", "13268", "13401", "13451" }

    )
)
Actuals for certain account numbers only

$ Actual (Prev Period)

CALCULATE([Actual (YTD)], PREVIOUSMONTH('Calendar'[Date]))


Actual

Actual 
Var Output = SUM(GLQ[UM_ACTUALS_CURR])
RETURN
IF(Output = 0, BLANK(), Output)

Actual (FY - 1)

Actual (FY - 1) 
VAR PreviousFiscalYear =
    CALCULATE (
        VALUE ( MAX ( 'Calendar'[Fiscal Year Number] ) ),
        PARALLELPERIOD ( 'Calendar'[Date], -1, YEAR )
    )
RETURN
    CALCULATE (
        [Actual],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Fiscal Year Number] = CONVERT ( PreviousFiscalYear, STRING )
        )
    )
Same as a project to date amount. Need to pick accounting period.
Actual (FY - 2)
Actual (FY - 2) 
VAR PreviousFiscalYear =
    CALCULATE (
        VALUE ( MAX ( 'Calendar'[Fiscal Year Number] ) ),
        PARALLELPERIOD ( 'Calendar'[Date], -2, YEAR )
    )
RETURN
    CALCULATE (
        [Actual],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Fiscal Year Number] = CONVERT ( PreviousFiscalYear, STRING )
        )
    )

Actual (YTD)

Actual (YTD) 
VAR Output =
    TOTALYTD (
        [Actual],
        'Calendar'[Date],
        FILTER (
            ALL ( 'Period Type' ),
            'Period Type'[Period Type] IN { "Normal Period", "Beginning Of Year Balance" }
        ),
        "06/30"
    )
RETURN
    IF ( Output = 0, BLANK (), Output )

Actual (YTD FY - 1)

Actual (YTD FY - 1) 
CALCULATE (
    [Actual],
    SAMEPERIODLASTYEAR ( DATESYTD ( 'Calendar'[Date], "06/30" ) )
)

Actual Net Change (YTD)

[Actual (YTD)]- [Actual (YTD FY - 1)]


Custom field

Field Name CalculationNotes

Wp Ref Column

SWITCH (
    TRUE (),
    'GLQ'[Group Column] = "1.STAR", "CF-B.1",
    RIGHT ( RELATED ( Department[Department ID] ), 6 ) = "000060"
        && RELATED ( Account[Account Number]) = "13107"
        && ISBLANK ( RELATED ( Class[Class] ) ), "CF-AA.2",
    RELATED ( Account[Account Number] ) = "13107", "CF-B.3",
    RELATED ( Account[Account Number] ) = "13121", "CF-B.7",
    RELATED ( Account[Account Number] ) = "13001"
        && RELATED ( Department[Department ID] ) = "5720100", "CF-B.4",
    RELATED ( Account[Account Number] ) = "13001"
        && RELATED ( Department[Department ID] ) = "1704000", "CF-B.5",
    RELATED ( Account[Account Number] ) = "13002"
        && RELATED ( Class[Class] ) = "90"
        && RELATED ( Department[Department ID] ) = "6704000", "CF-B.6",
    RELATED ( Account[Account Number] ) = "16000"
        && RELATED ( Department[Department ID] ) = "4310000", "CF-B.8",
    RELATED ( Account[Account Number] ) = "13100"
        && RELATED ( Department[Department ID] ) = "7606101", "CF-B.9",
    RELATED ( Account[Account Number] ) = "13100"
        && RELATED ( Class[Class] ) = "MC"
        && RELATED ( Department[Department ID] ) IN { "8260100", "8260110" }, "CF-B.10",
    RELATED ( Account[Account Number] ) = "16007"
        && RELATED ( Department[Department ID] ) = "7013100", "CF-B.13",
     RELATED ( Account[Account Number] ) = "16100"
        && ISBLANK ( RELATED ( Class[Class] ) )
        && RELATED ( Department[Department ID] ) = "5013100", "CF-B.14",
    RELATED ( Account[Account Number] ) = "13160", "CF-B.2",
    RELATED ( Account[Account Number] ) = "13150", "RU-FSN12",
    RELATED ( Account[Account Number] ) = "13800", "CF-D",    
    BLANK ()
)

For "YE Accounts Receivable" report.

Group Column

SWITCH (
    TRUE (),
    LEFT ( RELATED ( Account[Account Number] ), 3 ) = "160", "6.SUSPENSE",
    LEFT ( RELATED ( Account[Account Number] ), 3 ) = "161", "7.ISIS Clr",
    RELATED ( Account[Account Number] ) = "13001", "2.AR AGCY",
    RELATED ( Account[Account Number] ) = "13003"
        && RELATED ( Class[Class] ) = "SG", "2.AR AGCY",
    RELATED ( Account[Account Number] ) = "13107"
        && ISBLANK ( RELATED ( Class[Class] ) )
        && RIGHT ( RELATED ( Department[Department ID] ), 6 ) = "000060", "3.AP AR",
    RELATED ( Account[Account Number] ) = "13107"
        && RELATED ( Class[Class] ) = "TC"
        && RIGHT ( RELATED ( Department[Department ID] ), 6 ) = "000060", "4.TCARD AR",
    RELATED ( Account[Account Number] ) = "13000", "1.STAR",
    RELATED ( Account[Account Number] ) = "13003"
        && RELATED ( Department[Department ID] )
            IN { "1704000", "2710001", "3600000", "4310000", "5720100", "6704000", "7606101" }, "1.STAR", 
    "5.Other AR"
)

For "YE Accounts Receivable" report.

Wp Ref 2 Column

SWITCH (
    TRUE (),
    RELATED ( Fund[Fund Code] )
        IN { "24" }
            && RELATED ( Project[Project ID] ) IN { "5405314", "5405708", "5405944" }, "PF-B50",
    RELATED ( Fund[Fund] )
        IN { "00", "12" }
            && RELATED ( Account[Account Number] ) IN { "13300" }, "NA",
    GLQ[Group 2 Column] = "Grant Receivable - State Cap Approp", "PF-B30",
    GLQ[Group 2 Column] = "Grants Receivable", "RF-B",
    RELATED ( Account[Account Sub-Category] )
        IN { "Suspense", "Gross Accounts Receivable", "State Appropriation Receivable" }
            && RELATED ( Fund[Fund Code] ) IN { "00", "03", "04", "10", "12", "13" }, "CF-B",
    RELATED ( Fund[Fund Code] )
        IN { "20", "25", "23" }
            && RELATED ( Account[Account Number] )
                IN { "13100", "13118", "13122", "16000", "13107", "13150", "16010", "16105" }, "RF-B.2",
    RELATED ( Fund[Fund Code] )
        IN { "28", "29" }
            && RELATED ( Account[Account Number] ) IN { "13107", "13168" }, "PF-B30",
    RELATED ( Fund[Fund Code] )
        IN { "18" }
            && RELATED ( Account[Account Number] ) IN { "13168", "13107", "13160" }, "PF-DI.2",
    GLQ[Group 2 Column]
        IN {
        "Gross Pledges Receivable",
        "Allowance for Uncollectible Pledges Receivable",
        "Discount on Pledges Receivable"
    }, "RU-E",
    GLQ[Group 2 Column] = "Allowance for Doubtful Accounts", "CF-B",
    BLANK ()
)
For "YE RU-B Accounts, Grants and Pledges Receivable" report

Group 2 Column

SWITCH (
    TRUE (),
    RELATED ( Fund[Fund Code] ) = "89", "Grant Rec - Fund 89",
    RELATED ( Account[Account Sub-Category] )
        IN { "Suspense", "Gross Accounts Receivable", "State Appropriation Receivable" }
            && RELATED ( Fund[Fund Code] ) IN { "00", "03", "04", "10", "12", "13" }, "AR - Curr Fd Student and Other AR",
    RELATED ( Fund[Fund Code] )
        IN { "20", "23", "25" }
            && RELATED ( Account[Account Number] )
                IN { "13100", "13118", "13122", "16000", "16010", "16105", "13107", "13150" }, "AR - Restricted Fund",
    RELATED ( Fund[Fund Code] ) = "29"
        && RELATED ( Account[Account Number] ) IN { "13168", "13107" }, "AR - Plant Fund - Debt Service",
    RELATED ( Fund[Fund Code] )
        IN { "28", "18" }
            && RELATED ( Account[Account Number] ) IN { "13168", "13107", "13160" }, "AR - Plant Fund - Unexpended",
    RELATED ( Account[Account Sub-Category] )
)
For "YE RU-B Accounts, Grants and Pledges Receivable" report

Sort Group 2

SWITCH(TRUE(),
GLQ[Group 2 Column] IN {"AR - Curr Fd Student and Other AR", "AR - Plant Fund - Debt Service", "AR - Plant Fund - Unexpended", "AR - Restricted Fund"}, 1,
GLQ[Group 2 Column] IN {"Grant Receivable - State Cap Approp", "Grants Receivable"}, 2,
GLQ[Group 2 Column] IN {"Gross Pledges Receivable"}, 3,
GLQ[Group 2 Column] IN {"Allowance for Doubtful Accounts", "Allowance for Uncollectible Pledges Receivable"}, 4,
GLQ[Group 2 Column] IN {"Discount on Pledges Receivable"}, 5, 
6)
For "YE RU-B Accounts, Grants and Pledges Receivable" report. It is sort order for "Group 2 Column" field above.
class_descr_8550100
SWITCH (
    TRUE (),
      RELATED (Class[Class]) = "EA", "Patfa Interface",
      RELATED (Class[Class]) = "HT", "Hire Touch",
	  RELATED (Class[Class]) = "TW", "Compensation Study",
      RELATED (Class[Class]) = "15", "Hoonuit",
	  RELATED (Class[Class]) = "16", "EO Invest Coord",
      RELATED (Class[Class]) = "WC", "WorldClass Bench",
	  RELATED (Class[Class]) = "22", "Customer Exp",
      RELATED (Class[Class]) = "24", "Stipends",
	  RELATED (Class[Class]) = "25", "Wellness Mgr Sev",
      RELATED (Class[Class]) = "26", "CL3 Leave Coord",
	  RELATED (Class[Class]) = "27", "HR Info Specialist",
      RELATED (Class[Class]) = "28", "Telephone tree, transport doc, & cubicles",
	  RELATED (Class[Class]) = "JY", "UM Sev",
      RELATED (Class[Class]) = "MB", "UMFK Sev",
	  ISBLANK ( RELATED (Class[Class]) ) &&  RELATED ( Account[Account Number]) = "54810" , "Stipends",
	  RELATED (Class[Class]) = "02", "IT-LO Process",
      RELATED (Class[Class]) = "03", "IT-CompTime",
      RELATED (Class[Class]) = "04", "IT-I9",
      RELATED (Class[Class]) = "06", "IT-Retire File Interface",
	  RELATED (Class[Class]) = "07", "IT-Auto Ben Billing",
      RELATED (Class[Class]) = "08", "IT-Tuition Waiver",
	  RELATED (Class[Class]) = "09", "IT-Add Pay",
      RELATED (Class[Class]) = "10", "IT POI Form Auto",
	  RELATED (Class[Class]) = "11", "IT-PAF Auto",
      RELATED (Class[Class]) = "12", "IT-Terms On",
	  RELATED (Class[Class]) = "19", "IT-LMS",
      RELATED (Class[Class]) = "30", "UMA BP",
	  RELATED (Class[Class]) = "31", "Prof Devel",
      RELATED (Class[Class]) = "32", "Course Devel-Peak Learning",
	  RELATED (Class[Class]) = "33", "Empl Ben Dir.",
      RELATED (Class[Class]) = "CC", "Jessica Bauer",
      RELATED (Class[Class]) = "DM", "Kristin Hurd Data Entry",
	  RELATED (Class[Class]) = "CL", "LEBLANC",
      RELATED (Class[Class]) = "JS", "Josh StLewis",
      RELATED (Class[Class])
)
Description for Class_fld for DEPTID = 8550100

Attachments:

image2021-10-26_12-1-34.png (image/png)