Matrix view with months as columns and calculation based on that column

[OlivierC]

Hello,

Quite new to Power BI and DAX, so apologies if I ask something stupid.

I have a list of projects, each with a startdate. I'm looking for a way to build a matrix with the projects as rows, and 12 months as columns, starting with the current month. The values would have to be the number of days since the startdate of the project, but for the month of that column. If this seems a bit silly, this is not the endgoal, but it is a value I need for further calculations.

Something like this:

(startdate is in dd/mm/yyyy)

Note that the month in the column is not always januari, but the current month, and that the project startdate can be earlier than the start of the months that are being visualised.

That probably means simple YearToDate like formula's are are insufficient.

If feels like I need something like a measure, because the calculation is dependent of the context it is visualised in, but the calculation is also row-based, cause I need these number of days of each project individually, so that points towards a calculated column.

I'm having trouble wrapping my head around how to address the calculation that seems row based, but is also dependant on the filter context depicted by the current column.

Thanks in advance.

[Greg_Deckler]

This is a measure. You want a separate table of month dates (for your columns). You will grab the MAX of your month date table in a VAR. Use MAX to grab your start date of your project in another VAR. Use DATEDIFF with those two VAR's and a third parameter of DAY.

DaysPastStart = 
VAR __Month = MAX('Dates'[Date])
VAR __Start = MAX('Projects'[Start Date])
RETURN
DATEDIFF(__Start, __Month, DAY)

[OlivierC]

Thanks for your reply.

Unfortunately, this only results in a number of days in the same months as the startdates of the projects.

When you see the output of the __Start variable, that result is understandable.


I don't know how to get this startDate as variable value for each and every column of the row though...

[Greg_Deckler]

Understandable, most measure formulas are worthless without knowing the context in which they are executing. To answer this, need sample/example data.  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

[OlivierC]

Thank you for taking the time to reply, and my apologies for the late reaction. I have taken another route for now. Most of the hard work is done in the application that provides the data that is used, and Power BI is mainly used for visualisation. This is not taking advantage of the power of this tool, but I was getting nowhere...

I'm still interested in the solution though, so I can learn from it for possible future requests, so I'll provide the requested sample data.

Projects is just a list of projects, each with a startdate:

id	name	categoryId	startDate	endDate
1	Development project 1	17532	2018-01-01 00:00:00	2018-06-30 00:00:00
2	Development project 2	17532	2018-07-01 00:00:00	2018-12-31 00:00:00
3	Development project 3	17532	2018-04-01 00:00:00	2018-09-30 00:00:00
4	Animatie Project 1	13422	2018-01-01 00:00:00	2018-06-30 00:00:00
5	Animatie Project 2	13422	2018-07-01 00:00:00	2018-12-31 00:00:00
6	Animatie Project 3	13422	2018-04-01 00:00:00	2018-09-30 00:00:00
7	Animatie Project 4	13422	2018-10-01 00:00:00	2018-12-31 00:00:00
8	Development project Inactive	17532	2018-04-01 00:00:00	2018-09-30 00:00:00
9	Animatie project Inactive	13422	2018-04-01 00:00:00	2018-09-30 00:00:00
10	Pipeline project	19180	2018-04-01 00:00:00	2018-09-30 00:00:00
11	Admin project	18800	2018-07-01 00:00:00	2018-12-31 00:00:00

Then I have a dates table, which is also quite straightforward. I'll just post some sample rows here:

Date	Year	Month Number	Month	Year-Month	Month Year
2018-01-01 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-02 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-03 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-04 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-05 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-06 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-07 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-08 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-09 00:00:00	2018	1	januari	2018-01	januari 2018
2018-01-10 00:00:00	2018	1	januari	2018-01	januari 2018

The dates table stretches out way before and beyond the startDates of the projects, and there is a relation between the startDate of the projects and the date in the dates table.

Thank you.