Update a cell based on another column cell

[ncp_powerbi]

Hi,

We are collecting annual data. My table is set up such that muni and year are my record's id. 

When we collect data for the current year (example 2024), we would ask about their expenses for 2023. In the example below, record id 1001 had a total of 10000 expenses for 2023. How do I update the 2023 expense cell to whatever value is in the temp_expenses_prev collected in 2024?

This is my expected output.

Note that muni and year variables are both slicers. 

Any help is greatly appreciated.

Thank you.

[Ray_Minds]

Hi  @ncp_powerbi 

Please find my solution as below and let me know in case of any issue.

Overview: - I have loaded the dataset provide by you in the 'Order' table, which looks like below snapshot: -

Please find the solution steps as below: - 
 
1.  Create a Measure for Total Units on Order : First, you need to create a measure that calculates the total units on order for each product. 

2. Create a Measure for 60% of Total Units : Next, create a measure to determine 60% of the total units on order for each product.

3. Calculate Cumulative Sum of Units on Order : To determine the cumulative sum of units on order by shipped date, create a new measure: 

4.  Create a Measure for Ship Date : Now, create a measure that finds the minimum shipped date when the cumulative units exceed or equal 60% of the total units on order. 

5.  Create a New Table : Finally, create a new table to show the desired output. You can use the following DAX formula in the "New Table" option: 

This will give you a new table with the product, total units on order, and the date when at least 60% of the units on order have shipped.  

You can now visualize this result in your Power BI reports! Which will look like below snapshot: - 

If this solution resolves your issue, you can let us know by marking my post as a solution which can help the other community members to find it more quickly. 

[Anonymous · Accepted solution]

Hi @ncp_powerbi ,

I made a sample for you with a measure.

expense = 
VAR _year=MAX('Table1'[year])+1
VAR _muni=MAX('Table1'[muni])
VAR _value=CALCULATE( SUM(Table1[temp_expenses_prev]),'Table1'[year]=_year,'Table1'[muni]=_muni)
RETURN _value

Best Regards,

Wearsky

[ncp_powerbi]

Hello,

Thank you for the response. Yet I cant seem to make it work.

I will be calculating the previous year's expenses based on the current year.  So for example, I have new data in 2024 and save that 2023 data in the variable temp_expenses_prev. 

How do I modify your code above such that the value in temp_expenses_prev will be copied to the previous year expense column? this is my expected output

muni	year	expense	temp_expenses_prev
1001	2023	10000
1001	2024		10000
1002	2022	20000
1002	2023	30000	20000
1002	2024		30000

[danextian]

Hi @ncp_powerbi ,

Try the following:

Prev Year = 
CALCULATE (
    SUM ( 'Table'[Expense] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[muni] = EARLIER ( 'Table'[muni] )
            && 'Table'[year]
                = EARLIER ( 'Table'[year] ) - 1
    )
)

Also, going forward, please post a sample data that can be copy pasted as text and not an image.