Sales by mounth from one table

[Anonymous]

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?

[amitchandak]

Please refer to my blog , how to deal with two dates. Hope it helps, a part of your problem

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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-wi...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[MFelix]

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

[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

[Anonymous]

@MFelix 

Forgot to tag you in the latest post.

[Anonymous]

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?