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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bekzat
New Member

accrual based profit

Good day,

spent a lot of time looking for a solution, but no luck

I have a table with the following data 

Client    Date_begin        Date_end            Termination_date          Sales$

AAA       01.06.2021          31.05.2022                                                   500

BBB        01.01.2022          31.12.2022                                                   400

BBB                                                                       30.04.2022              -100

CCC        03.05.2022          02.08.2022                                                   100

 


(today()-sum(Table[date_end]))/
(in sales$ column negative sales mean refund, in case refund made all sales are considered earned on an accrual basis)


What i am trying to do

1. add a column (accual_coeff) with the following formula
if there exists data in termination_date, 1, otherwise 

(sum(Table[date_end])-sum(Table[date_begin]))
2. Add a measure that is the multiplication of accrual_coeff and Sales$

As a result i'd have

Client    Sales$   accural coeff      Accued Profit

AAA       500         0.97                        483.5

BBB        300         1.00                        300.0

CCC        100         0.18                        17.6


I can't do it in power query by grouping, since this table is a small part of a big table, and i am going to use with multiple other combinations
Any help would be greatly appreciated

 

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @Bekzat ,

 

Sorry, not very clear. 

I have a few questions for you, the accual_coeff of AAA is 0.97, how did you get it? Is there a specific formula to provide?

Table[date_end] is a date, how to sum up?

vstephenmsft_1-1653298614602.png

 

 

 

Best Regards,

Stephen Tao

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.