Forum Discussion

saanah2019's avatar
saanah2019
Helper 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. 

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
Helper 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
      Helper 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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.