Finance - Balance Sheet Reports

Download Sample cloud_download Download Excel Data cloud_download

Finance - Balance Sheet Reports

This sample provides insights on the following balance sheet metrics:

  1. Accounts Receivable by Location (P&L), and Client
  2. Accrued Revenue by Location (P&L), and Client
  3. Net Working Capital in Total =
  • Accrued Revenue/Other Assets
  • Net Debtors
  • Work In Progress
  • Trade Creditors/Accurals
  • Deferred Income

Dashboard - Snippet

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Data Model - Snippet

Click to view large-sized image

Click to view large-sized image

Measures – Aged Accrued

% Accrued Revenue Overdue

DAX 
% Accrued Revenue Overdue = 
IFERROR (
    [Total Aged Accrued Revenue Overdue] / [Total Aged Accrued Revenue],
    BLANK ()
)

% AR Var to AR Target

DAX 
% AR Var to AR Target = 
[% Accrued Revenue Overdue] 
- [Aged AR Overdue % Target]

AR: 0 - 30 Days

DAX 
AR: 0 - 30 Days = 
IF (
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "0 - 30 Days"
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "0 - 30 Days"
    )
)

AR: 121 - 180 Days

DAX 
AR: 121 - 180 Days = 
IF (
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "121 - 180 Days"
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "121 - 180 Days"
    )
)

AR: 181 Days - 1 Year

DAX 
AR: 181 Days - 1 Year = 
IF (
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "181 Days - 1 Year"
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "181 Days - 1 Year"
    )
)

AR: 31 - 60 Days

DAX 
AR: 31 - 60 Days = 
IF (
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "31 - 60 Days"
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "31 - 60 Days"
    )
)

AR: 61 - 90 Days

DAX 
AR: 61 - 90 Days = 
IF (
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "61 - 90 Days"
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "61 - 90 Days"
    )
)

AR: 91 - 120 Days

DAX 
AR: 91 - 120 Days = 
IF (
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "91 - 120 Days"
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "91 - 120 Days"
    )
)

AR: 91 - 180 Days

DAX 
AR: 91 - 180 Days = 
[AR: 91 - 120 Days] + [AR: 121 - 180 Days]

AR: Not Due Yet

DAX 
AR: Not Due Yet = 
IF (
    [Total Aged Accrued Revenue] - [Total Aged Accrued Revenue Overdue] = 0,
    BLANK (),
    [Total Aged Accrued Revenue] - [Total Aged Accrued Revenue Overdue]
)

AR: Over 1 Year

DAX 
AR: Over 1 Year = 
IF (
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "Over 1 Year"
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "Over 1 Year"
    )
)

Total Aged Accrued Revenue

DAX 
Total Aged Accrued Revenue = 
IF (
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "Total Accrued Revenue"
    ) = 0,
    BLANK (),
    CALCULATE (
        SUM ( Accrued_Revenue[Value] ),
        Accrued_Revenue[Attribute] = "Total Accrued Revenue"
    )
)

Total Aged Accrued Revenue Overdue

DAX 
Total Aged Accrued Revenue Overdue = 
IF (
    [AR: 61 - 90 Days] + [AR: 91 - 180 Days] + [AR: 181 Days - 1 Year] + [AR: Over 1 Year] = 0,
    0,
    [AR: 61 - 90 Days] + [AR: 91 - 180 Days] + [AR: 181 Days - 1 Year] + [AR: Over 1 Year]
)

Measures – Aged Debtors

% Debtor Var to Debtor Target

DAX 
% Debtor Var to Debtor Target = 
[% Debtors Overdue] 
- [Aged Debtor Overdue % Target]

% Debtors Overdue

DAX 
% Debtors Overdue = 
IFERROR(
[Total Debtors Overdue]/[Total Net Debtors], BLANK())

0 - 30 Days

DAX 
0 - 30 Days = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "0 - 30 Days"
    )=0,
BLANK(),
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "0 - 30 Days"
    )
)

181 Days - 1 Year

DAX 
181 Days - 1 Year = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "181 Days - 1 Year"
    )=0,
BLANK(),
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "181 Days - 1 Year"
    )
)

31 - 60 Days

DAX 
31 - 60 Days = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "31 - 60 Days"
    )=0,
BLANK(),
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "31 - 60 Days"
    )
)

61 - 90 Days

DAX 
61 - 90 Days = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "61 - 90 Days"
    )=0,
BLANK(),
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "61 - 90 Days"
    )
)

91 - 180 Days

DAX 
91 - 180 Days = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "91 - 180 Days"
    )=0,
BLANK(),
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "91 - 180 Days"
    )
)

Not Due Yet

DAX 
Not Due Yet = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "Not Due Yet"
    )=0,
BLANK(),
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "Not Due Yet"
    )
)

Over 1 Year

DAX 
Over 1 Year = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "Over 1 Year"
    )=0,
BLANK(),
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "Over 1 Year"
    )
)

Total Debtors Overdue

DAX 
Total Debtors Overdue = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "Total Overdue"
    )=0,
0,
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "Total Overdue"
    )
)

Total Net Debtors

DAX 
Total Net Debtors = 
IF(
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "Total Net Debtors"
    )=0,
BLANK(),
CALCULATE (
        SUM ( Aged_Debtors[Value] ),
        Aged_Debtors[Attribute] = "Total Net Debtors"
    )
)

Measures – BS All Measures

BS All Measures

DAX 
BS All Measures = 
CALCULATE (
    SUM ( 'BS_Measures'[Value] )
)

CM - PM BS All Measures

DAX 
CM - PM BS All Measures = 
[BS All Measures] - [PM BS All Measures]

CM - PY BS All Measures

DAX 
CM - PY BS All Measures = 
[BS All Measures] - [PY BS All Measures]

CM - PY LM BS All Measures

DAX 
CM - PY LM BS All Measures = 
[BS All Measures] - [PY LM BS All Measures]

PM - CM BS All Measures

DAX 
PM - CM BS All Measures = 
[PM BS All Measures] - [BS All Measures]

PM BS All Measures

DAX 
PM BS All Measures = 
CALCULATE (
    [BS All Measures],
    DATEADD(Calendar_Table[Date],-1,MONTH)
)

PY - CM BS All Measures

DAX 
PY - CM BS All Measures = 
[PY BS All Measures] - [BS All Measures]

PY BS All Measures

DAX 
PY BS All Measures = 
CALCULATE (
    [BS All Measures],
    SAMEPERIODLASTYEAR(Calendar_Table[Date])
)

PY LM - CM BS All Measures

DAX 
PY LM - CM BS All Measures = 
[PY LM BS All Measures] - [BS All Measures]

PY LM BS All Measures

DAX 
PY LM BS All Measures = 
VAR selectedyear =
    SELECTEDVALUE ( Calendar_Table[Year] )
RETURN
    CALCULATE (
        [BS All Measures],
        FILTER (
            ALL ( Calendar_Table ),
            Calendar_Table[Year] = selectedyear - 1
                && Calendar_Table[MonthName] = "December"
        )
    )

Measures – BS Measures

12M Accrued Revenue / Other Asset

DAX 
12M Accrued Revenue / Other Assets = 
CALCULATE (
    IF (
        CALCULATE (
            SUM ( 'BS_Measures'[Value] ),
            'BS_Measures'[Account] = "AC=OALY"
                || 'BS_Measures'[Account] = "AC=OAMY"
                || 'BS_Measures'[Account] = "AC=STCKS",
            FILTER (
                ALL ( Calendar_Table ),
                Calendar_Table[Sequential Day Number]
                    > MAX ( Calendar_Table[Sequential Day Number] ) - 366
                    && Calendar_Table[Sequential Day Number]
                        <= MAX ( Calendar_Table[Sequential Day Number] )
            )
        ) = 0,
        BLANK (),
        CALCULATE (
            SUMX (
                SUMMARIZE ( BS_Measures, BS_Measures[Account] ),
                AVERAGEX (
                    SUMMARIZE (
                        BS_Measures,
                        BS_Measures[Account],
                        BS_Measures[Account Name],
                        BS_Measures[Client Code],
                        BS_Measures[Time Bucket Description],
                        BS_Measures[Date],
                        "Value", SUM ( BS_Measures[Value] )
                    ),
                    [Value]
                )
            ),
            'BS_Measures'[Account] = "AC=OALY"
                || 'BS_Measures'[Account] = "AC=OAMY"
                || 'BS_Measures'[Account] = "AC=STCKS",
            DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

12M Deferred Income

DAX 
12M Deferred Income = 
VAR Account = "AC=DI"
RETURN
CALCULATE (
    IF (
        CALCULATE (
            SUM ( 'BS_Measures'[Value] ),
            'BS_Measures'[Account] = Account,
            FILTER (
                ALL ( Calendar_Table ),
                Calendar_Table[Sequential Day Number]
                    > MAX ( Calendar_Table[Sequential Day Number] ) - 366
                    && Calendar_Table[Sequential Day Number]
                        <= MAX ( Calendar_Table[Sequential Day Number] )
            )
        )
            = 0,
        BLANK (),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account,
            DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
) * -1

12M Net Debtors (Gross Less Provision)

DAX 
12M Net Debtors (Gross Less Provision) = 
VAR Account = "AC=NDLP"
RETURN
CALCULATE (
    IF (
        CALCULATE (
            SUM ( 'BS_Measures'[Value] ),
            'BS_Measures'[Account] = Account,
            FILTER (
                ALL ( Calendar_Table ),
                Calendar_Table[Sequential Day Number]
                    > MAX ( Calendar_Table[Sequential Day Number] ) - 366
                    && Calendar_Table[Sequential Day Number]
                        <= MAX ( Calendar_Table[Sequential Day Number] )
            )
        )
            = 0,
        BLANK (),
            CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account,
            DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

12M Rolling NWC Ratio [%]

DAX 
[12M Rolling NWC Ratio [%]]] = 
VAR AccountNum = "AC=NWC"
VAR AccountDenom = "AC=TB"
RETURN
IFERROR (
    CALCULATE (
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = AccountNum
            )
                = 0,
            BLANK (),
            CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = AccountNum,
            DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
            )
        ),
        FILTER ( Calendar_Table, Calendar_Table[Date] >= [Last N Months] )
    )
        / CALCULATE (
            IF (
                CALCULATE (
                    SUM ( 'BS_Measures'[Value] ),
                    'BS_Measures'[Account] = AccountDenom
                )
                    = 0,
                BLANK (),
                CALCULATE (
                    SUM ( 'BS_Measures'[Value] ),
                    'BS_Measures'[Account] = AccountDenom,
                    DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
                )
            ),
            FILTER ( Calendar_Table, Calendar_Table[Date] >= [Last N Months] )
        ),
    BLANK ()
)

12M Rolling NWC Ratio [%] last

DAX 
[12M Rolling NWC Ratio [%]] last] = 
VAR AccountNum = "AC=NWC"
VAR AccountDenom = "AC=TB"
RETURN
CALCULATE(
IFERROR (
    CALCULATE (
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = AccountNum
            )
                = 0,
            BLANK (),
            CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = AccountNum,
            DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
            )
        ),
        FILTER ( Calendar_Table, Calendar_Table[Date] >= [Last N Months] )
    )
        / CALCULATE (
            IF (
                CALCULATE (
                    SUM ( 'BS_Measures'[Value] ),
                    'BS_Measures'[Account] = AccountDenom
                )
                    = 0,
                BLANK (),
                CALCULATE (
                    SUM ( 'BS_Measures'[Value] ),
                    'BS_Measures'[Account] = AccountDenom,
                    DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
                )
            ),
            FILTER ( Calendar_Table, Calendar_Table[Date] >= [Last N Months] )
        ),
    BLANK ()
), 
FILTER(Calendar_Table, Calendar_Table[Date] = [Last Month with Value]))

12M Total Billing & Fees

DAX 
12M Total Billing & Fees = 
VAR Account = "AC=TB"
RETURN
CALCULATE (
    IF (
        CALCULATE (
            SUM ( 'BS_Measures'[Value] ),
            'BS_Measures'[Account] = Account,
            FILTER (
                ALL ( Calendar_Table ),
                Calendar_Table[Sequential Day Number]
                    > MAX ( Calendar_Table[Sequential Day Number] ) - 366
                    && Calendar_Table[Sequential Day Number]
                        <= MAX ( Calendar_Table[Sequential Day Number] )
            )
        )
            = 0,
        BLANK (),
            CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account,
            DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

12M Total NWC

DAX 
12M Total NWC = 
[12M Accrued Revenue / Other Assets]+
[12M Net Debtors (Gross Less Provision)]+
[12M WIP]+
[12M Trade Creditors / Accruals]+
[12M Deferred Income]

12M Total NWC / Total Billing & Fees Ratio [%]

DAX 
[12M Total NWC / Total Billing & Fees Ratio [%]]] = 
DIVIDE ( [12M Total NWC], [12M Total Billing & Fees], BLANK () )

12M Trade Creditors / Accruals

DAX 
12M Trade Creditors / Accruals = 
CALCULATE (
    IF (
        CALCULATE (
            SUM ( 'BS_Measures'[Value] ),
            'BS_Measures'[Account] = "AC=FSIO"
                    || 'BS_Measures'[Account] = "AC=FSUGL"
                    || 'BS_Measures'[Account] = "AC=MA"
                    || 'BS_Measures'[Account] = "AC=OACLY"
                    || 'BS_Measures'[Account] = "AC=OACMY"
                    || 'BS_Measures'[Account] = "AC=OTCA"
                    || 'BS_Measures'[Account] = "AC=TCLY"
                    || 'BS_Measures'[Account] = "AC=TCMY",
            FILTER (
                ALL ( Calendar_Table ),
                Calendar_Table[Sequential Day Number]
                    > MAX ( Calendar_Table[Sequential Day Number] ) - 366
                    && Calendar_Table[Sequential Day Number]
                        <= MAX ( Calendar_Table[Sequential Day Number] )
            )
        ) = 0,
        BLANK (),
        CALCULATE (
            SUMX (
                SUMMARIZE ( BS_Measures, BS_Measures[Account] ),
                AVERAGEX (
                    SUMMARIZE (
                        BS_Measures,
                        BS_Measures[Account],
                        BS_Measures[Account Name],
                        BS_Measures[Client Code],
                        BS_Measures[Time Bucket Description],
                        BS_Measures[Date],
                        "Value", SUM ( BS_Measures[Value] )
                    ),
                    [Value]
                )
            ),
            'BS_Measures'[Account] = "AC=FSIO"
                    || 'BS_Measures'[Account] = "AC=FSUGL"
                    || 'BS_Measures'[Account] = "AC=MA"
                    || 'BS_Measures'[Account] = "AC=OACLY"
                    || 'BS_Measures'[Account] = "AC=OACMY"
                    || 'BS_Measures'[Account] = "AC=OTCA"
                    || 'BS_Measures'[Account] = "AC=TCLY"
                    || 'BS_Measures'[Account] = "AC=TCMY",
            DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

12M WIP

DAX 
12M WIP = 
VAR Account = "AC=WIP"
RETURN
CALCULATE (
    IF (
        CALCULATE (
            SUM ( 'BS_Measures'[Value] ),
            'BS_Measures'[Account] = Account,
            FILTER (
                ALL ( Calendar_Table ),
                Calendar_Table[Sequential Day Number]
                    > MAX ( Calendar_Table[Sequential Day Number] ) - 366
                    && Calendar_Table[Sequential Day Number]
                        <= MAX ( Calendar_Table[Sequential Day Number] )
            )
        )
            = 0,
        BLANK (),
            CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account,
            DATESINPERIOD ( Calendar_Table[Date], MAX ( 'BS_Measures'[Date] ), -12, MONTH )
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Accrued Revenue / Other Assets

DAX 
Accrued Revenue / Other Assets = 
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = "AC=OALY"
                    || 'BS_Measures'[Account] = "AC=OAMY"
                    || 'BS_Measures'[Account] = "AC=STCKS"
            ) = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = "AC=OALY"
                    || 'BS_Measures'[Account] = "AC=OAMY"
                    || 'BS_Measures'[Account] = "AC=STCKS"
            )
        ),
        CALCULATE (
            SUMX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account]
                ),
                AVERAGEX (
                    SUMMARIZE (
                        BS_Measures,
                        BS_Measures[Account],
                        BS_Measures[Account Name],
                        BS_Measures[Client Code],
                        BS_Measures[Time Bucket Description],
                        BS_Measures[Date],
                        "Value", SUM ( BS_Measures[Value] )
                    ),
                    [Value]
                )
            ),
            'BS_Measures'[Account] = "AC=OALY"
                    || 'BS_Measures'[Account] = "AC=OAMY"
                    || 'BS_Measures'[Account] = "AC=STCKS"
            )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Accrued Revenue > 60 Days [%]

DAX 
[Accrued Revenue > 60 Days [%]]] = 
DIVIDE (
     ( [Accrued Revenue: 61 - 180 Days] + [Accrued Revenue: 180+ Days] ),
    [Accrued Revenue: Total],
    BLANK ()
)

Accrued Revenue: < 60 Days

DAX 
Accrued Revenue: < 60 Days = 
VAR TimeBucket = "Less than 60 days"
VAR Account = "AC=GAR"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Time Bucket Description] = TimeBucket,
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Accrued Revenue: 180+ Days

DAX 
Accrued Revenue: 180+ Days = 
VAR TimeBucket = "180+ days"
VAR Account = "AC=GAR"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Time Bucket Description] = TimeBucket,
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Accrued Revenue: 61 - 180 Days

DAX 
Accrued Revenue: 61 - 180 Days = 
VAR TimeBucket = "61 - 180 days"
VAR Account = "AC=GAR"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Time Bucket Description] = TimeBucket,
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Accrued Revenue: Total

DAX 
Accrued Revenue: Total = 
VAR TimeBucket = "Total"
VAR Account = "AC=GAR"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Time Bucket Description] = TimeBucket,
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Avg Annual NWC

DAX 
Avg Annual NWC = 
CALCULATE (
    SUMX (
        SUMMARIZE (
            BS_Measures,
            BS_Measures[Location Code],
            BS_Measures[Location Name],
            BS_Measures[Account],
            BS_Measures[Account Name],
            BS_Measures[Client Code],
            BS_Measures[Time Bucket Description],
            Calendar_Table[Date].[Year],
            "Value", AVERAGE ( BS_Measures[Value] )
        ),
        [Value]
    ),
    'BS_Measures'[Account] = "AC=NWC"
)

Deferred Income

DAX 
Deferred Income = 
VAR Account = "AC=DI"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)* -1

Last Month with Value

DAX 
Last Month with Value = 
CALCULATE (MAX ( 'BS_Measures'[Date] ), ALL ( 'BS_Measures'), 'BS_Measures'[Value] <> 0 )

Last N Months

DAX 
Last N Months = 
EDATE ( [Last Month with Value], - 'Last N Months'[Last N Months Value] + 1)

Net Debtors (Gross Less Provision)

DAX 
Net Debtors (Gross Less Provision) = 
VAR Account = "AC=NDLP"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Net Working Capital

DAX 
Net Working Capital = 
VAR Account = "AC=NWC"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Net Working Capital PY

DAX 
Net Working Capital PY = 
VAR Account = "AC=NWC"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account,
                SAMEPERIODLASTYEAR(Calendar_Table[Date])
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account,
                SAMEPERIODLASTYEAR(Calendar_Table[Date])

            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account,
            SAMEPERIODLASTYEAR(Calendar_Table[Date])

        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

NWC Target Var [%] = [12M Rolling NWC Ratio [%]] last] - 0.10

DAX 
[NWC Target Var [%]]] = [12M Rolling NWC Ratio [%]] last] - 0.10

Total Billings & Fees

DAX 
Total Billings & Fees = 
VAR Account = "AC=TB"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Trade Creditors / Accruals

DAX 
Trade Creditors / Accruals = 
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = "AC=FSIO"
                    || 'BS_Measures'[Account] = "AC=FSUGL"
                    || 'BS_Measures'[Account] = "AC=MA"
                    || 'BS_Measures'[Account] = "AC=OACLY"
                    || 'BS_Measures'[Account] = "AC=OACMY"
                    || 'BS_Measures'[Account] = "AC=OTCA"
                    || 'BS_Measures'[Account] = "AC=TCLY"
                    || 'BS_Measures'[Account] = "AC=TCMY"
            ) = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = "AC=FSIO"
                    || 'BS_Measures'[Account] = "AC=FSUGL"
                    || 'BS_Measures'[Account] = "AC=MA"
                    || 'BS_Measures'[Account] = "AC=OACLY"
                    || 'BS_Measures'[Account] = "AC=OACMY"
                    || 'BS_Measures'[Account] = "AC=OTCA"
                    || 'BS_Measures'[Account] = "AC=TCLY"
                    || 'BS_Measures'[Account] = "AC=TCMY"
            )
        ),
        CALCULATE (
            SUMX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account]
                ),
                AVERAGEX (
                    SUMMARIZE (
                        BS_Measures,
                        BS_Measures[Account],
                        BS_Measures[Account Name],
                        BS_Measures[Client Code],
                        BS_Measures[Time Bucket Description],
                        BS_Measures[Date],
                        "Value", SUM ( BS_Measures[Value] )
                    ),
                    [Value]
                )
            ),
            'BS_Measures'[Account] = "AC=FSIO"
                || 'BS_Measures'[Account] = "AC=FSUGL"
                || 'BS_Measures'[Account] = "AC=MA"
                || 'BS_Measures'[Account] = "AC=OACLY"
                || 'BS_Measures'[Account] = "AC=OACMY"
                || 'BS_Measures'[Account] = "AC=OTCA"
                || 'BS_Measures'[Account] = "AC=TCLY"
                || 'BS_Measures'[Account] = "AC=TCMY"
            )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
) * -1

Trade Debtors (> 61 days) as [%] of Total

DAX 
[Trade Debtors (> 61 days) as [%]] of Total] = 
DIVIDE (
     ( [Trade Debtors: 61 - 180 Days] + [Trade Debtors: 180+ Days] ),
    [Trade Debtors: Total],
    BLANK ()
)

Trade Debtors > 60 Days [%]

DAX 
[Trade Debtors > 60 Days [%]]] = DIVIDE (
         ( [Trade Debtors: 61 - 180 Days] + [Trade Debtors: 180+ Days] ),
        [Trade Debtors: Total],
        BLANK ()
    )

Trade Debtors: < 60 Days

DAX 
Trade Debtors: < 60 Days = 
VAR TimeBucket = "Less than 60 days"
VAR Account = "AC=TD"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Time Bucket Description] = TimeBucket,
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Trade Debtors: 180+ Days

DAX 
Trade Debtors: 180+ Days = 
VAR TimeBucket = "180+ days"
VAR Account = "AC=TD"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Time Bucket Description] = TimeBucket,
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Trade Debtors: 61 - 180 Days

DAX 
Trade Debtors: 61 - 180 Days = 
VAR TimeBucket = "61 - 180 days"
VAR Account = "AC=TD"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Time Bucket Description] = TimeBucket,
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Trade Debtors: Total

DAX 
Trade Debtors: Total = 
VAR TimeBucket = "Total"
VAR Account = "AC=TD"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Time Bucket Description] = TimeBucket,
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Time Bucket Description] = TimeBucket,
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Work In Progress

DAX 
Work In Progress = 
VAR Account = "AC=WIP"
RETURN
CALCULATE (
    IF (
        HASONEFILTER ( Calendar_Table[Date].[Month] ),
        IF (
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
                = 0,
            BLANK (),
            CALCULATE (
                SUM ( 'BS_Measures'[Value] ),
                'BS_Measures'[Account] = Account
            )
        ),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    BS_Measures,
                    BS_Measures[Account],
                    BS_Measures[Account Name],
                    BS_Measures[Client Code],
                    BS_Measures[Time Bucket Description],
                    BS_Measures[Date],
                    "Value", SUM ( BS_Measures[Value] )
                ),
                [Value]
            ),
            'BS_Measures'[Account] = Account
        )
    ),
    FILTER (
        Calendar_Table,
        Calendar_Table[Date] >= [Last N Months]
            && Calendar_Table[Date] <= [Last Month with Value]
    )
)

Measures – Other

% Total Aged Overdue

DAX 
% Total Aged Overdue = 
IFERROR (
    [Total Aged Debtors & AR Overdue [$]]] / [Total Aged Debtors & AR [$]]],
    BLANK ()
)

Colour KPI Background AR Overdue %

DAX 
Colour KPI Background AR Overdue % = 
SWITCH (
    TRUE (),
    [% Accrued Revenue Overdue] < [Aged AR Overdue % Target], "#eefbeb",
    "#fbd4d4"
)

Colour KPI Background Debtors Overdue %

DAX 
Colour KPI Background Debtors Overdue % = 
SWITCH (
    TRUE (),
    [% Debtors Overdue] < [Aged Debtor Overdue % Target], "#eefbeb",
    "#fbd4d4"
)

Colour KPI Font AR Overdue %

DAX 
Colour KPI Font AR Overdue % = 
SWITCH (
    TRUE (),
    [% Accrued Revenue Overdue] < [Aged AR Overdue % Target], "#518508",
    "#fd2c2c"
)

Colour KPI Font Debtors Overdue %

DAX 
Colour KPI Font Debtors Overdue % = 
SWITCH (
    TRUE (),
    [% Debtors Overdue] < [Aged Debtor Overdue % Target], "#518508",
    "#fd2c2c"
)

Month and Year of Aged Accrued Revenue

DAX 
Month and Year of Aged Accrued Revenue = 
FORMAT (
    CALCULATE (
        LASTDATE ( Accrued_Revenue[Date])
    ),
    "MMM YY"
)

Month and Year of Aged Debtors

DAX 
Month and Year of Aged Debtors = 
FORMAT (
    CALCULATE (
        LASTDATE ( Aged_Debtors[Date])
    ),
    "MMM YY"
)

Total Aged 0 - 30 Days

DAX 
Total Aged 0 - 30 Days = 
[0 - 30 Days] + [AR: 0 - 30 Days]

Total Aged 181 Days - 1 Year

DAX 
Total Aged 181 Days - 1 Year = 
[AR: 181 Days - 1 Year] + [AR: Over 1 Year]

Total Aged 31 - 60 Days

DAX 
Total Aged 31 - 60 Days = 
[31 - 60 Days] + [AR: 31 - 60 Days]

Total Aged 61 - 90 Days

DAX 
Total Aged 61 - 90 Days = 
[61 - 90 Days] + [AR: 61 - 90 Days]

Total Aged 91 - 180 Days

DAX 
Total Aged 91 - 180 Days = 
[91 - 180 Days] + [AR: 91 - 180 Days]

Total Aged Debtors & AR [$]

DAX 
[Total Aged Debtors & AR [$]]] = 
[Total Net Debtors] + [Total Aged Accrued Revenue]

Total Aged Debtors & AR Overdue [$]

DAX 
[Total Aged Debtors & AR Overdue [$]]] = 
[Total Debtors Overdue] + [Total Aged Accrued Revenue Overdue]

Total Aged Not Due Yet

DAX 
Total Aged Not Due Yet = 
[Not Due Yet] + [AR: Not Due Yet]

Total Aged Over 1 Year

DAX 
Total Aged Over 1 Year = 
[Over 1 Year] + [AR: Over 1 Year]

Measures – Calendar

Selected Month

DAX 
Selected Month = 
VAR selectedytdmonth =
    FORMAT ( SELECTEDVALUE ( Calendar_Table[Date].[Month] ), "MMMM" )
RETURN
    "" & IF ( ISBLANK ( selectedytdmonth ), "Multiple Months", selectedytdmonth )

Selected Year

DAX 
Selected Year = 
VAR selectedyear =
    SELECTEDVALUE ( Calendar_Table[Year] )
RETURN
    "" & IF ( ISBLANK ( selectedyear ), "Multiple Years", selectedyear )

Measures – Client Exposure Targets

Aged (Debtor & AR) Overdue % Target

DAX 
Aged (Debtor & AR) Overdue % Target = 
DIVIDE (
     ( [Aged AR Overdue % Target] * [Total Aged Accrued Revenue] + [Aged Debtor Overdue % Target] * [Total Net Debtors] ),
     ( [Total Aged Accrued Revenue] + [Total Net Debtors] ),
    BLANK ()
)

Aged AR Overdue % Target

DAX 
Aged AR Overdue % Target = 
CALCULATE (
    AVERAGE ( Client_Exposure_Targets[Value] ),
    Client_Exposure_Targets[Target] = "Target Aged AR Overdue [%]"
)

Aged Debtor Overdue % Target

DAX 
Aged Debtor Overdue % Target = 
CALCULATE (
    AVERAGE ( Client_Exposure_Targets[Value] ),
    Client_Exposure_Targets[Target] = "Target Aged Debtors Overdue [%]"
)

Measures – Client List

Selected Client

DAX 
Selected Client = 
VAR selectedclient =
    SELECTEDVALUE ( Client_List[Client Description] )
RETURN
    "" & IF ( ISBLANK ( selectedclient ), "Multiple Clients", selectedclient )

Measures – Geographic Table

No. of Locations

DAX 
No. of Locations = 
CALCULATE (
    DISTINCTCOUNT ( Geographic_Table[Location Code] ),
    Geographic_Table[Master Location] <> "All Locations"
)

Selected Location

DAX 
Selected Location = 
VAR selectedlocation =
    SELECTEDVALUE ( Geographic_Table[Master Location] )
RETURN
    "" & IF ( ISBLANK ( selectedlocation ), "Multiple Locations", selectedlocation )

Measures – Last N Months

Last N Months Value

DAX 
Last N Months Value = SELECTEDVALUE('Last N Months'[Last N Months])

Calculated Columns

MonthName

DAX 
MonthName = FORMAT(Calendar_Table[Date],"MMMM")

Tables

Calendar_Table

DAX 
Calendar_Table = 
  GENERATE ( 
    CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2023, 12, 31 ) ), 
    VAR currentDay = [Date]
    VAR Day = DAY( currentDay )
    VAR Month =  MONTH ( currentDay ) 
    VAR Year =  YEAR ( currentDay )
    VAR SequentialDayNumber = INT ([Date])
  RETURN   ROW ( 
    "Day", day, 
    "Month", month, 
    "Year", year,
    "Sequential Day Number", SequentialDayNumber)
  )

Last N Months

DAX 
Last N Months = GENERATESERIES(1, 24, 1)