Calculated Column to Calculate Date Per ID When Cumulative Sales Exceeds Target in Power BI

[tdanielspbi]

Hello Power BI Community,

I'm working on a project where I need to create a calculated column that calculates the date per ID when the cumulative sales have exceeded their respective target. I have two tables, Table 1 (budgets per ID) and the Sales Table (sales transactions). There is a one-to-many relationship between these tables.

Here's a sample of the data I'm working with:

Table 1 (Budgets per ID):

ID Target

1	1000
2	1500
3	2000

Sales Table (Sales Transactions):

ID Date Sales

1	2023-01-01	300
1	2023-01-02	400
1	2023-01-03	350
2	2023-01-01	500
2	2023-01-02	600
2	2023-01-03	450
3	2023-01-01	800
3	2023-01-02	700
3	2023-01-03	600

I need a calculated column in Table 1 to show the date when the cumulative sales per ID have exceeded the target.

Any suggestions on how to create this calculated column in Power BI?

Thank you in advance for your help!

[FreemanZ]

hi @tdanielspbi 

try to add a calculated column like:

Date = 
MINX(
    FILTER(
        RELATEDTABLE(Sales),
        VAR _date = Sales[Date]
        RETURN
            CALCULATE(
                SUM(Sales[Sales]),
                Sales[Date]<=_date,
                ALLEXCEPT(Sales,Sales[ID])
            )>=[Target]
    ),
    Sales[Date]
)

i slightly changed your Table1[Target] column to better verify the code. it worked like: