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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
eriwet
Frequent Visitor

How to associate columns with a certain quarter

Hi, this seems to me like a trivial task, but I fail to figure out how to do it in a simple and straight forward way.

 

I'd like to evaluate the accuracy of a forecast against the actual outcome.  I have the forecasted volumes for some part numbers in one table (Table 1 below), with each part number having the forecasted total volume per quarter in four different columns.

The actual output have the same part numbers and the actual call-offs of those part numbers - one row for the total volume per day. (Table 2)

Now, I'd like to visualize the accuracy of the forecast by subtracting the sum of the actual call-offs per part number for each quarter.

I have made an auto date/time table in which I can get the Quarter and connect to the the call-off table, but how do I associate the columns in the first table to each quarter?

I'd lika also to maintin the possibility to see the difference on a more aggregated level (i.e. for all quarters at the same time).

eriwet_0-1700500000410.png

 



1 ACCEPTED SOLUTION
ray_aramburo
Super User
Super User

You will necessarily need to have a date column in table 1 in order to match the periods with table 2. I know sometimes forecast are flat values but you can use either the first or last day of the quarter/month as reference. 





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





View solution in original post

4 REPLIES 4
eriwet
Frequent Visitor

Thanks, quite simple to unpivot - thanks for the advice. 

ray_aramburo
Super User
Super User

You will necessarily need to have a date column in table 1 in order to match the periods with table 2. I know sometimes forecast are flat values but you can use either the first or last day of the quarter/month as reference. 





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Ok, sounds reasonable, but since each row in Table 1 has four different columns with forecasted volumes (one per quarter) would I then have to transform Table 1 to have four rows for each part number?

That's right, you can unpivot it on Power Query.





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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