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.
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.
Solved! Go to Solution.
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.
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.
Thanks alot. I will mark your answer as the solution. Also, you created the Date table for every single day but I just want a date table with the month and year,
jan 2020
feb 2020
march 2020
... Is that going to affect anything?
It won't affect anything. I added every day because you gave the dates with a day number, and if they do have day numbers every date should exist in the dimension table. It won't affect anything, but if you do only need months you can feel free to trim down the table.
Ok thanks, I will figure out how to trim my data to make it MM YYYY but how do I do that with the Date table you created?
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.
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |