Search code examples
powerbidaxpowerbi-desktopdaxstudio

Generate Dates Between in Power BI

I have a table that contains the fields: name, contract start date, contract end date

name       date_start      date_end

Helen      01.01.2018      01.08.2018

Peter      02.03.2018      03.04.2018

George     01.01.2018      06.08.2019

Lisa       03.04.2018      08.05.2018

Ann        01.03.2018      07.06.2018

I need to see who had contracts in effect at the beginning of each month of 2018. I also need a column with the start date of each month.

WHAT I need: For example, for Helen the contract is valid from January to August.
I expect:

Helen   01.01.18    01.08.18    01.01.18
Helen   01.01.18    01.08.18    01.02.18
Helen   01.01.18    01.08.18    01.03.18
Helen   01.01.18    01.08.18    01.04.18
Helen   01.01.18    01.08.18    01.05.18
Helen   01.01.18    01.08.18    01.06.18
Helen   01.01.18    01.08.18    01.07.18
Helen   01.01.18    01.08.18    01.08.18

in SQl, it looks like this:

SELECT dates.month_start_date,
       mytable.*
  FROM (SELECT ADD_MONTHS(TO_DATE('01/01/2018','MM/DD/YYYY'),LEVEL-1) month_start_date
          FROM dual
         CONNECT BY LEVEL <= 12) dates,
       mytable
 WHERE dates.month_start_date BETWEEN mytable.date_start AND mytable.date_end

In power bi I have the calendar table, named "of_date"

I was trying to get the expected result using DAX:

        Calculated table1 = 
    
    /*var name = CALCULATETABLE(
        SUMMARIZE('TABLE1',  'TABLE1'[NAME]),
     'OF_DATE'[DATE]>= MAX('TABLE1'[DATE_START]) && 'OF_DATE'[DATE] <= MAX('TABLE1'[DATE_END]))
    
    var dt = CALCULATETABLE(SUMMARIZE('OF_DATE', 'OF_DATE'[DATE]))
    var result = UNION(name, dt)
    
    return result*/
    

    VAR SelectedDate =
        SELECTEDVALUE ( 'OF_DATE'[DATE])
    RETURN
        CALCULATETABLE( ( SELECTEDVALUE ('TABLE1'[NAME]), 'TABLE1'[DATE_START]<=SelectedDate && 'TABLE1'[DATE_END] >= SelectedDate)
    )

My attempts did not lead to the expected result

how can I realize what I'm expecting?

Solution

  • enter image description here

    Create a date table if you don't have one already. Mine looks like this:

    Date = CALENDARAUTO()

    Create a new calculated table:

    Table 2 = 
FILTER(
    GENERATE('Table', DATESBETWEEN('Date'[Date], 'Table'[date_start], 'Table'[date_end])),
    DAY( 'Date'[Date]) = 1
)