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.
Hi,
I was hoping someone is able to advise me as to how to deal with my new challenge. I have a table of contracts. Each row is a contract with start date, end date, volume and colour.
Start date | End date | Volume | Colour |
01-Jan-18 | 01-Dec-18 | 1 | Red |
01-Jan-18 | 01-Sep-18 | 1 | Green |
01-Feb-18 | 01-Mar-18 | 2 | Purple |
01-Jan-18 | 01-Apr-18 | 4 | Red |
01-May-18 | 01-Dec-18 | 1 | Blue |
01-Feb-18 | 01-May-18 | 5 | Orange |
01-Feb-18 | 01-May-18 | 2 | Blue |
01-Feb-18 | 01-May-18 | 4 | Red |
I would like to plot the data on a column chart by month. The columns would be stacked in each month to give the break-down by colour. For that sake, I would create a calendar table. Shall I create a daily calendar table using CALENDAR() or a monthly calendar table? I don't know how to do the latter but I think it would look like this:
Date | Volume | Colour |
Jan-18 | 1 | Red |
Feb-18 | 1 | Red |
Mar-18 | 1 | Red |
Apr-18 | 1 | Red |
May-18 | 1 | Red |
Jun-18 | 1 | Red |
Jul-18 | 1 | Red |
Aug-18 | 1 | Red |
Sep-18 | 1 | Red |
Oct-18 | 1 | Red |
Nov-18 | 1 | Red |
Dec-18 | 1 | Red |
Jan-18 | 1 | Green |
Feb-18 | 1 | Green |
Mar-18 | 1 | Green |
Apr-18 | 1 | Green |
May-18 | 1 | Green |
Jun-18 | 1 | Green |
Jul-18 | 1 | Green |
Aug-18 | 1 | Green |
Sep-18 | 1 | Green |
Feb-18 | 2 | Purple |
Mar-18 | 2 | Purple |
Jan-18 | 4 | Red |
Feb-18 | 4 | Red |
Mar-18 | 4 | Red |
Apr-18 | 4 | Red |
May-18 | 1 | Blue |
Jun-18 | 1 | Blue |
Jul-18 | 1 | Blue |
Aug-18 | 1 | Blue |
Sep-18 | 1 | Blue |
Oct-18 | 1 | Blue |
Nov-18 | 1 | Blue |
Dec-18 | 1 | Blue |
Feb-18 | 5 | Orange |
Mar-18 | 5 | Orange |
Apr-18 | 5 | Orange |
May-18 | 5 | Orange |
Feb-18 | 2 | Blue |
Mar-18 | 2 | Blue |
Apr-18 | 2 | Blue |
May-18 | 2 | Blue |
Feb-18 | 4 | Red |
Mar-18 | 4 | Red |
Apr-18 | 4 | Red |
May-18 | 4 | Red |
I guess once I have that table, plotting the column chart is trivial. But how do I get that table? And is that a good approach to the problem?
I guess what I am trying to plot would look like this:
Thanks for your help and advice
Solved! Go to Solution.
Rather than doing it in DAX, I'd do it in Query Editor using "M".
1. Add new blank query, name it "fnExpand" and paste in following using Advanced Editor.
(sDate as date, eDate as date)=> let Source = List.Generate(()=>sDate, each _ <= eDate, each Date.AddMonths(_,1)) in Source
Then in the original table query, Add Column -> Invoke Custom Function.
Then expand to new rows and change data type.
EDIT: I made error in original changed < eDate to <=eDate.
Rather than doing it in DAX, I'd do it in Query Editor using "M".
1. Add new blank query, name it "fnExpand" and paste in following using Advanced Editor.
(sDate as date, eDate as date)=> let Source = List.Generate(()=>sDate, each _ <= eDate, each Date.AddMonths(_,1)) in Source
Then in the original table query, Add Column -> Invoke Custom Function.
Then expand to new rows and change data type.
EDIT: I made error in original changed < eDate to <=eDate.
Thank you, this is a powerful approach! I have implemented your instructions and it looks encouraging but I have one issue. In my dataset, the start dates and end dates are often the beginning of the month (i.e. 01/mm/yy) but not always. They can be any day of the year as it happens in some cases (e.g. 14/mm/yy). I want fnExpand to be months starting on the first day of the month e.g. if a contract has a volume of 3650 units, start date = 15/05/18 and end date = 14/07/18, then it should expand to 01/05/18 for May-18, 01/06/18 for Jun-18, 01/07/18 for Jul-18 and the volume would be 170, 300, 140 units in each of those expanded months respectively. How can I achieve that? I guess it is possible to tweak your query in the Advanced Editor but I don't know how to do that.
Finally but crucially, a related question is how to plot a time series with monthly granularity when I have all the data on the first day of the months i.e. how to plot the contracts vs time showing 170 units in May-18, 300 units in Jun-18, 140 units in Jul-18. If the answer to this question is that Power BI cannot do charts with monthly granularity on a continuous horizontal axis and that I should use daily granularity, I will need to tweak your query fnExpand to expand into days rather than months, which will multiply the size of my dataset by 30 and is something I want to avoid for the sake of efficiency unless it is strictly necessary. Please let me know your thoughts on this. All my data visualization is by month. No need to drill into days unless Power BI requires that for technical reasons.
Thank you so much!
PS: I edited the subject of this post to better reflect what we are discussing
I'm not entirely clear on the logic here.
"e.g. if a contract has a volume of 3650 units, start date = 15/05/18 and end date = 14/07/18, then it should expand to 01/05/18 for May-18, 01/06/18 for Jun-18, 01/07/18 for Jul-18 and the volume would be 170, 300, 140 units in each of those expanded months respectively."
To plot time series with monthly granularity, or to perform any sort of time intelligence on data set. I'd start by creating date dimension table. Holding each date of calendar year(s) along with any other date related dimensions and build relationship to the fact table (based on [Date]).
Typically I build it using DAX. Starting from DimDate = CALENDAR(Start Date, End Date)
This will create table with single date column, holding each date from start to end. Then I add calculated columns to suite my need.
Ex:
Then I use appropriate column to plot or calculate measure.
If you need to expand to daily level using fnExpand, you can use Date.AddDays(_, 1) instead of Date.AddMonths(_, 1)
If you want, you can create dimension table using "M" as well.
You'd use fnExpand with Date.AddDays(). Then invoke function outside of query. It will generate list with all dates. Then add any additional columns desired, and perform any transformation needed.
If you need further help, it would be helpful to upload sample data set. Along with expected result (manually generated/inputted).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |