Update a cell based on another column cell

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.

Need Help

Anonymous

1 year ago

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

2024-10-28.pbix18 KB

4 Replies

danextian

Super User

1 year ago

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.

ncp_powerbi
Frequent Visitor
1 year ago
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

Anonymous

Not applicable

1 year ago

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

2024-10-28.pbix18 KB

Ray_Minds
Solution Supplier
1 year ago
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.