Forum Discussion
get the months from 2 dates
| Have | |||||||||||||
| Name | Start | End | Points | ||||||||||
| Sam | 1/1/2020 | 3/1/2020 | 24 | ||||||||||
| Smith | 2/1/2020 | 6/1/2020 | 54 | ||||||||||
| Warner | 3/1/2020 | 9/1/2020 | 49 | ||||||||||
| Labuschagne | 5/1/2020 | 12/1/2020 | 80 | ||||||||||
| Want | |||||||||||||
| Name | January | February | March | April | May | June | July | August | September | October | November | December | |
| Sam | 8 | 8 | 8 | ||||||||||
| Smith | 9 | 9 | 9 | 9 | 9 | 9 | |||||||
| Warner | 7 | 7 | 7 | 7 | 7 | 7 | 7 | ||||||
| Labuschagne | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | |||||
I want to create basically a time table of the people and their projects over the course of the year.
How can I achieve this? Thank you.
The Calendar function will only create a table with all days- it's made that way so it works with Power Bi's built-in time intelligence functions. If you want to avoid this, you'd have to create your own table. The easiest way is probably just to create an excel book with a row for each month with "Datetime , MonthNumber , MonthName" and import the data from there. Do keep in mind that the datetime object does need to include a day, probably the first of the month, so that Power Bi recognizes it as a datetime.
8 Replies
- I have a startdate and EndDate and points.I want to create basically a time table of the people and their projects over the course of the year.
How can I achieve this? Thank you. I don't have much to work with here in terms of what your tables look like, but this will accomplish what you want. I called your source table 'Table'.
Create a date dimension table for 1 year,
DATE = CALENDAR(Date(2020,1,1), Date(2020,12,1))
Create a measure, let's call it PointDisplay =
var pts = CALCULATE (MAX('Table'[Points]) / (DATEDIFF(MAX('Table'[Start]), MAX('Table'[End]), Month)+1)
RETURN
IF( AND ( MONTH(MAX('Date'[Date])) >= MONTH(MAX('Table'[Start])), MONTH(MAX('Date'[Date])) <= MONTH(MAX('Table'[End])) ),
pts,
Blank() )
Now create a matrix. Put the person name in the rows. Put the month name column from the date dimension table in the columns. Put the PointDisplay measure in the values. That matrix should be what you wanted, although without a description of how points are calculated etc. it's a bit hard to be sure.
Hi thanks for your answer. Unfortunately your measure is not working for me, it gives me an error that " the syntax for the return is incorrect"
Start Date End Date Person Points 12/23/2019 1/12/2020 Sam 50 1/13/2020 1/19/2020 Bas 40 1/20/2020 2/9/2020 Cas 60 2/10/2020 3/1/2020 Las 20 3/2/2020 3/22/2020 Das 40 3/23/2020 4/12/2020 Sas 45 4/13/2020 5/3/2020 Ras 85 5/4/2020 5/24/2020 Dun 55 5/25/2020 6/14/2020 Bun 60 6/15/2020 7/5/2020 Sun 70 7/6/2020 7/26/2020 Lun 80 7/27/2020 8/16/2020 Mun 90 8/17/2020 9/6/2020 Nun 60 9/7/2020 9/27/2020 Par 10 9/28/2020 10/18/2020 Bar 25 10/19/2020 11/8/2020 Rar 30 My data is essentially this, just dates, the name of the person and numbers for points.
Paste the following directly into the formula bar. The syntax should be correct- it works in the pbix I created.
Points Measure =var pts = CALCULATE(Max('Table'[Points]) / (DATEDIFF(Max('Table'[Start]), MAX('Table'[End]), MONTH)+1) )RETURNIF(AND( MONTH(MAX('Date'[Date])) >= MONTH(MAX('Table'[Start])),MONTH(MAX('Date'[Date])) <= MONTH(MAX('Table'[End]))),pts,BLANK())Make sure that you create a date dimension table with matching names, and update this measure to use the proper name and column names for your original table.
