Extrapolate arrears amount from existing figures and display one week's data and YTD in same table

[StevenHarrison]

Hi Guys, I have been given a spreadsheet to convert into a Power BI report, it displays the current (or selected) fiscal week and also has YTD figures in adjacent columns.

This data now comes directly from our instance of Microsoft Dynamics 365.

Please can you tell me how I would display one week's worth of data and YTD data in the following format - Green box has current or selected week's data and red box has the total so far for the YTD amounts:

Also need to extrapolate what the future percentage for Current and Net HB Arrears rent arrears could be,

the Excel formula example for a Current arrears calculation:
=D14/(J14/K$7*52)

1. D14 is current or selected fiscal week number for the current arrears figure for a housing officer.
2. J14 is the total YTD figure for a Housing Officer.
3. K$7 is the selected Fiscal week number.
4. *52 is just the 52 weeks in the year

How can I achieve this in DAX?

Any help much appreciated.

[v-zhouwen-msft · Accepted solution]

Hi @StevenHarrison ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Current Arrears Net of HB & SINV = MAX('Table'[Current Arrears]) - MAX('Table'[Anticipated HB]) - MAX('Table'[Outstanding Rent SINV])

2.Use the following DAX expression to create a measure(Calculation of YTD data based on slicer selection)

Debit Charged = CALCULATE(SUM('Table'[Current Arrears]),'Table'[Ended] <= SELECTEDVALUE('Table'[Ended]),ALLEXCEPT('Table','Table'[OFFICER]))

3.Use the following DAX expression to create a measure

CURRENT ARREARS % = DIVIDE(MAX('Table'[Current Arrears]),[Debit Charged])

4.Use the following DAX expression to create a measure

Net HB Arrears = DIVIDE(MAX('Table'[Anticipated HB]),[Debit Charged])

5.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhouwen-msft · Accepted solution]

Hi @StevenHarrison ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Current Arrears Net of HB & SINV = MAX('Table'[Current Arrears]) - MAX('Table'[Anticipated HB]) - MAX('Table'[Outstanding Rent SINV])

2.Use the following DAX expression to create a measure(Calculation of YTD data based on slicer selection)

Debit Charged = CALCULATE(SUM('Table'[Current Arrears]),'Table'[Ended] <= SELECTEDVALUE('Table'[Ended]),ALLEXCEPT('Table','Table'[OFFICER]))

3.Use the following DAX expression to create a measure

CURRENT ARREARS % = DIVIDE(MAX('Table'[Current Arrears]),[Debit Charged])

4.Use the following DAX expression to create a measure

Net HB Arrears = DIVIDE(MAX('Table'[Anticipated HB]),[Debit Charged])

5.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StevenHarrison · Accepted solution]

Thanks @v-zhouwen-msft , I'll take a look and report back, see if I can get my head around this 🙂