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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ValeriaBreve
Post Patron
Post Patron

Best way to shape data when they contain both yearly and monthly column

Dear experts,

I have a set of data that combines monthly columns and yearly columns (see below).

I see 2 options to use this:

1) Split the table in 2: one that has the yearly attributes, one that contains the monthly figures (that I will unpivot), and then connect these 2 tables in the report via the "Product" column 

2) Keep the table as is, unpivot the monthly figures, which will make the yearly figures repeat on multiple rows for the same product, and then use the yearly figures as "Average" on aggregation (they will give incorrect results if summed because they are repeated on several rows)

 

What's the best way of handling this situation? 

Thanks!

Kind regards

Valeria

ProductJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-222022 Forecast
A876068282168361043772134700
B44598528311155663308730500
C62831323369623329507612900
D748557885662799058100811100
E78196553351217899764467300
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@ValeriaBreve , Go with the first one, Split and have a year and monthly tables, and Unpivot both.

 

Now have date in both tables and join them with a common date table

 

Date = datevalues("01-"&[Month])

 

Date =date([Year],1,1) //or Date([Year],12,31)

 

then refer

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

 

 

refer if needed

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

 

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

View solution in original post

2 REPLIES 2
ValeriaBreve
Post Patron
Post Patron

Thank you so much! Great material 🙂

amitchandak
Super User
Super User

@ValeriaBreve , Go with the first one, Split and have a year and monthly tables, and Unpivot both.

 

Now have date in both tables and join them with a common date table

 

Date = datevalues("01-"&[Month])

 

Date =date([Year],1,1) //or Date([Year],12,31)

 

then refer

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

 

 

refer if needed

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

 

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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