Use "M" to expand dates in table

[Anonymous]

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

[Chihiro · Accepted 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.

[Anonymous]

@Chihiro

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

[Chihiro]

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)

[Chihiro]

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).