Unpivot Data for Income Statement

DAX DAX Calculations
1

Hi guys!

I´m trying to unpivot a table created in the data model (not from the query) and created some additional measures to merge it with the Income Statement Data.

image
image835×413 26.5 KB

The result needs to have 3 columns like the following example:

Category Date Values
COS - Beginning Inventory 9/1/2019 € -
COS - Ending Inventory 9/30/2019 € 367
COS - Beginning Inventory 10/1/2019 € 367
COS - Ending Inventory 10/30/2019 € 828
COS - Beginning Inventory 11/1/2019 € 828
COS - Ending Inventory 11/30/2019 € 203
COS - Beginning Inventory 12/1/2019 € 203
COS - Ending Inventory 12/31/2019 € 973
COS - Beginning Inventory 1/1/2020 € 973
COS - Ending Inventory 1/31/2020 € 429
COS - Beginning Inventory 2/1/2020 € 429
COS - Ending Inventory 2/29/2020 € 2,926

The final goal is to merge the Inventory Data into the Income Statement and have one standardized data table.

image
image699×431 23.3 KB

I appreciate the support!!

I attached the PBIX File for reference.

Cheers!

Example Data.pbix (345.4 KB)

2

HI @Yeriel

Try Something like this.

Create a table and use this dax expression.

Unpivot_Inventory =
VAR A =
SELECTCOLUMNS (
‘Inventory Data’,
“Category”, “COS-Begining”,
“Date”, ‘Inventory Data’[First Date],
“Values”, ‘Inventory Data’[Beginning Inventory €]
)
VAR B =
SELECTCOLUMNS (
‘Inventory Data’,
“Category”, “COS-Ending”,
“Date”, ‘Inventory Data’[Last Date],
“Values”, ‘Inventory Data’[Ending Inventory €]
)
RETURN
UNION ( A, B )

Hope this helps.

Regards,
Hemant

1 Like
3

Hi @Hemantsingh !

Yes, that works perfectly!!

I was using SUMMARIZE but I got a couple of errors.

Thanks for the help!

Have a great day!!

Cheers!

4

Happy to help mate…

regards

5

Hi @Hemantsingh

By any chance, you know how to fix the circular dependency?

When I try to create a relationship from the Dates Tables to the Income Statement Dates Columns, I get this error.

image
image1505×780 117 KB

image
I thought I had everything ready to finally be able to create the Income Statement :cry: but I got this error.

I attached the PBIX file for reference.

Example Data.pbix (319.4 KB)

Hope you can help me :pray:

6

HI @Yeriel

Are you able to resolve the circular dependency error?? Just Checked your message today. Was unable to connect due to weekend.

Regards,
Hemant

7

Hi @Hemantsingh

Yes at the end, the best way was to create a better model.

I’m starting from scratch to avoid any circular dependencies in the future.

Thanks for checking on me man.

I’ll keep you posted with the new data model :wink:.

Have a great day!

Yeriel

1 Like
8

Nice to hear that…Always happy to help…cheers

regards