Dynamic calculation of each row by month

[yshmelev]

Hello!

I want to create a chart in Power BI, but cannot find a right approach to do that.

I have a list of products with unique product ids. Each product_id has a field with arrival date, current status (if it's sold or not) and when it was sold (arrival date + days_in_store_before_sold). I want to show sales by month (or even by week), but each month/week should show 3 categories: if a product is sold during current month, if it is in store less than 30 days or if it's in store more than 30 days.

Data sample:

product_unique_id	status	arrived_to_store	days_in_store_before_sold	responsible_person	note_for_understanding
apple-green-123	sold	01/05/2022	5	Ken	in store Jan, sold Jan
peach-123	sold	01/10/2022	10	Mike	in store Jan, sold Jan
watermelon-123	sold	01/20/2022	30	Mike	in store Jan, sold Feb
apple-red-999999	in store	01/07/2022	n/a	Mike	in store Jan, not sold
watermelon-123	sold	02/02/2022	10	Mike	in store Feb, sold Feb
peach-999999999	sold	02/05/2022	35	Ken	in store Feb, sold Mar
peach-123	in store	03/03/2022	n/a	Mike	in store Mar, not sold

At the end I want something like that:

There are other fields, such as responsible_person, that show specific product attributes. That's why I don't want to remove it, I will need to put them as filters on the chart in the future.

As you can see, it's very dynamic from month to month. For example, apple-red-999999 is in "In store (0-30 days)" group in "end of January", but should be "In store (30+ days)" group in every other month after January (because it has never been sold). Same with peach-999999999: it's "In store (0-30 days)" by the end of February, but in "Sold" category by the end of March.

The chart is probably not the biggest problem, I'm struggling to find a way to create measures/columns to use in my final chart. Could you please advise me the right approach to achieve this? Or any links that cover similar cases...

[Anonymous · Accepted solution]

Hi @yshmelev ,

Please check the pbix pile.

If I have misunderstood your needs, please feel free to contact me, it would be nice to have the calculation logic.

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