Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
saanah2019
Helper II
Helper II

get the months from 2 dates

Have             
              
Name StartEndPoints         
              
Sam 1/1/20203/1/202024         
Smith 2/1/20206/1/202054         
Warner 3/1/20209/1/202049         
Labuschagne5/1/202012/1/202080         
              
              
              
              
Want             
              
Name JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
              
Sam 888         
Smith  999999     
Warner   7777777   
Labuschagne    1010101010101010
              

 


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.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

View solution in original post

8 REPLIES 8
saanah2019
Helper II
Helper II

I have a startdate and EndDate and points.
 
want.JPG
 
 
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.
 
 
 
 
Anonymous
Not applicable

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 DateEnd Date PersonPoints
12/23/20191/12/2020 Sam50
1/13/20201/19/2020 Bas40
1/20/20202/9/2020 Cas60
2/10/20203/1/2020 Las20
3/2/20203/22/2020 Das40
3/23/20204/12/2020 Sas45
4/13/20205/3/2020 Ras85
5/4/20205/24/2020 Dun55
5/25/20206/14/2020 Bun60
6/15/20207/5/2020 Sun70
7/6/20207/26/2020 Lun80
7/27/20208/16/2020 Mun90
8/17/20209/6/2020 Nun60
9/7/20209/27/2020 Par10
9/28/202010/18/2020 Bar25
10/19/202011/8/2020 Rar30

 

My data is essentially this, just dates, the name of the person and numbers for points. 

Anonymous
Not applicable

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) )
RETURN
IF(
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. 
 
 

 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? 

Anonymous
Not applicable

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?

Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.