cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OlivierC Frequent Visitor
Frequent Visitor

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

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:

2018-06-26 10_22_38-Map1 - Excel.png

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

4 REPLIES 4
Super User
Super User

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

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)

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

OlivierC Frequent Visitor
Frequent Visitor

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

Thanks for your reply.

Unfortunately, this only results in a number of days in the same months as the startdates of the projects.
2018-06-27 16_25_43-DatasetBoordtabel - Power BI Desktop.png


When you see the output of the __Start variable, that result is understandable.
2018-06-27 16_17_28-DatasetBoordtabel - Power BI Desktop.png

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

Super User
Super User

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

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

OlivierC Frequent Visitor
Frequent Visitor

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

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:

idnamecategoryIdstartDateendDate
1Development project 1175322018-01-01 00:00:002018-06-30 00:00:00
2Development project 2175322018-07-01 00:00:002018-12-31 00:00:00
3Development project 3175322018-04-01 00:00:002018-09-30 00:00:00
4Animatie Project 1134222018-01-01 00:00:002018-06-30 00:00:00
5Animatie Project 2134222018-07-01 00:00:002018-12-31 00:00:00
6Animatie Project 3134222018-04-01 00:00:002018-09-30 00:00:00
7Animatie Project 4134222018-10-01 00:00:002018-12-31 00:00:00
8Development project Inactive175322018-04-01 00:00:002018-09-30 00:00:00
9Animatie project Inactive134222018-04-01 00:00:002018-09-30 00:00:00
10Pipeline project191802018-04-01 00:00:002018-09-30 00:00:00
11Admin project188002018-07-01 00:00:002018-12-31 00:00:00

 

 

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

DateYearMonth NumberMonthYear-MonthMonth Year
2018-01-01 00:00:0020181januari2018-01januari 2018
2018-01-02 00:00:0020181januari2018-01januari 2018
2018-01-03 00:00:0020181januari2018-01januari 2018
2018-01-04 00:00:0020181januari2018-01januari 2018
2018-01-05 00:00:0020181januari2018-01januari 2018
2018-01-06 00:00:0020181januari2018-01januari 2018
2018-01-07 00:00:0020181januari2018-01januari 2018
2018-01-08 00:00:0020181januari2018-01januari 2018
2018-01-09 00:00:0020181januari2018-01januari 2018
2018-01-10 00:00:0020181januari2018-01januari 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.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 204 members 2,315 guests
Please welcome our newest community members: