Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

Use 1 dimension column for 2 measures in Matrix

Hi all,

I need a little help with the following. I have a fact table with production and sales. 1 Key date that is linked with the calendar table. 1 column with the max sales date. This max Sales date needs to be compared with all the mutations before that date.

So the last date column will be used as column in the matrix. So I will have 3 colums, 05-06-2023, 03-07-2023 and 31-07-2023. What I would like to see is a measure with 2 variables. 1 Production and 1 Sales. For column 05-06-2023, I would like to the SUM of amounts where the Date Column <= 05-06-2023 and type = production, for the second column 03-07-2023 I would like to the SUM of amounts where the Date Column <= 03-07-2023 and type = production and so on. See expected outcome. The second variable needs to be the sales.

Who could help me out?

Expected Outcome:

 5-6-2023 3-7-2023 31-7-2023 Production 500 600 800 Sales 400 550 850

Data:

 Type Date Amount LastDate _KeyProduct Production 20-5-2023 400 5-6-2023 1a Production 30-5-2023 100 3-7-2023 1a Production 8-6-2023 100 3-7-2023 1a Production 5-7-2023 100 31-7-2023 1a Production 15-7-2023 100 31-7-2023 1a Sales 20-5-2023 -250 1a Sales 5-6-2023 -150 1a Sales 8-6-2023 -150 1a Sales 5-7-2023 -150 1a Sales 15-7-2023 -150 1a
1 ACCEPTED SOLUTION
Super User

see attached

1 REPLY 1
Super User

see attached

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors