Forum Discussion
Sales by mounth from one table
Hi everyone,
I am new to the BI world, so excuse me if my question is poorly formulated and that the question might be simple.
I have created a Microsoft PowerApp in which various data are being stored for each possible project that we might conduct. All of this data ends up in one single long, ugly MainTable, which I have imported from OneDrive for Business to format and visualise.
Each project has a StartDate, duration in days, EndDate. According to those data and "CurrentBudget" each month of 2020, 2021,etc has it's own field (Jan2020,Feb2020,....Dec2020).
How do I go about connecting these fields to months of a year?
5 Replies
- MFelix
Super User
Hi Anonymous ,
When you are refering that each value has it's own field I'm assuming that you have a column for each of the month (Jan2020,Feb2020,....Dec2020) correct?
You need to unpivot the months columns and then use a calendar table to wrap everything in your visuals, but without any data example or expected result is difficult to give you a better answer.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).Regards,
MFelix
I apologise for the poor description, as I said I am new to the program.
1. Your first assumption was right, each month has its own column.
I have unpivoted the Month columns, but the problem is I have other fields such as "WeightedRevenue", "CurrenBudget", which gets multiplied by the month that the project spans over. This is a picture of what I mean:
"MonthRevenue" is the pivoted column, however, notice that "Current Budget", "Weighted Revenue", etc gets multiplied
How can I provide the actual data so that it will be easier to understand what I am talking about?
- MFelix
Hi Anonymous ,
I would make a split between two parts:
- Project Table: All information regarding budget, starting date, end date etc.
- Movements Table: with all the values that are uinique by month.
Then using a dimension table for calendar and the projects table would have the information.
You can make from the same source two different tables. If you can share a sample of your data in excel (just with a few rows/projects) I can setup a model for your as an example.
Regards,
MFelix
- amitchandak
Please refer to my blog , how to deal with two dates. Hope it helps, a part of your problem
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
