Grouping annual revenues in A if the date filter selected is before 7/1/2022; Otherwise in B

[Zeyu]

I want to calculate the annual revenue for the product A/B. A and B are the same product with different product name, and they are all sold by the company called XX.
For the months before July 2022, all the annual revenue for A and B should be grouped into A; For the months after July 2022, all the annual revenue for A and B should be grouped into B.

The Dax formula I used:  Annual Revenue = CALCULATE(SUM(Sheet1[Budget]),DATESBETWEEN(Sheet1[Date],STARTOFYEAR(Sheet1[Date]),ENDOFYEAR(Sheet1[Date])))

However, when I select Jan 2022, the matrix table still splits the annual revenue into both A and B.

Please advice if there is a way to achieve my goal. (I have tried to create two tables. One with Product name all in A and another one with Product all in B. Then use if statement to get the annual revenue. This way works but not ideal because the real dataset is very large and complicated).

Thank you for your support!

[amitchandak]

@Zeyu , Create a new column and use that

Switch( True(),

[Date] > Date(2022,07,31) && [product] in {"A", "B"}, "B",

[Date] <= Date(2022,07,31) && [product] in {"A", "B"}, "A",

[product]

) 

[Zeyu]

Hi Amitchandak,

Thank you for your answer!

However, after mimicing the code, the column is still showing A/B. Please correct me if I am wrong, I don't think we can create calculated column that can dynamically update based on the slicer you select.  

Please inform me if I missed anything in my code!

[v-cgao-msft]

Hi @Zeyu ,

You are correct, the calculated columns are stored in memory and are only recalculated when the memory is refreshed.

Check that the example in the pbix file is the output you want.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Zeyu]

Hi Gao,

Thank you for putting time into this.

However, the demo you shared is not exactly what I need in my case. The annual budget should always be the same. Only the product name should change based on the date you selected.

Annual budget = 1200 for product A and B for year 2022.

Date selected before july 2022, annual budget = 1200 and product = A

Date selected after july 2022, annual budget = 1200 and product = B

Please let me know if it is possible to schedule a meeting with the Power BI team.