Forum Discussion

saanah2019's avatar
saanah2019
Icon for Helper II rankHelper II
6 years ago
Solved

get the months from 2 dates

Have                                                       Name   Start End Points                                               Sam...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

saanah2019's avatar
saanah2019
Icon for Helper II rankHelper II
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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. 
 
 
  • saanah2019's avatar
    saanah2019
    Icon for Helper II rankHelper II
    6 years ago

     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's avatar
      Anonymous
      Not applicable
      6 years ago

      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. 

      • saanah2019's avatar
        saanah2019
        Icon for Helper II rankHelper II
        6 years ago

        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?