Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
StevenHarrison
Resolver I
Resolver I

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

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:

StevenHarrison_0-1713170458993.png

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.

2 ACCEPTED SOLUTIONS
v-zhouwen-msft
Community Support
Community Support

Hi @StevenHarrison ,

The Table data is shown below:

vzhouwenmsft_0-1713236729839.png

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

vzhouwenmsft_1-1713236978262.png

vzhouwenmsft_2-1713236989871.png

vzhouwenmsft_3-1713237006790.png

 

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.

View solution in original post

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

View solution in original post

2 REPLIES 2
v-zhouwen-msft
Community Support
Community Support

Hi @StevenHarrison ,

The Table data is shown below:

vzhouwenmsft_0-1713236729839.png

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

vzhouwenmsft_1-1713236978262.png

vzhouwenmsft_2-1713236989871.png

vzhouwenmsft_3-1713237006790.png

 

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.

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.