Forum Discussion

Hennadii's avatar
Hennadii
Icon for Helper IV rankHelper IV
6 years ago
Solved

Create a table with dates based on Start / End Dates and Values on another Table

Hi there, I have a Period table with Star Date and End Date columns. Periods are not overlap each other, so each date is related to certain Period. Period Start Date End Date period 1 01/0...
Date
DAX
power bi
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Perhaps:

     

     

    Calendar Table = 
  ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),
    "Period",MAXX(FILTER('Table',[Start Date]<=[Date] && [End Date]>=[Date]),[Period])
  )

    Hennadii 

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Hennadii ,

Try

Create a date table
Date = calendar(Min(Period[Start Date]),Max(Period[End Date]))

 

Add a new column in that

Period = minx(filter(period, Period[Start Date]<=Date[Date] && Period[End Date]>=Date[Date]),Period[Period])

Hennadii's avatar
Hennadii
Icon for Helper IV rankHelper IV
6 years ago

Thank you amitchandak  and Greg_Deckler  !!!

amitchandak, your detailed sample helped me to understand Greg's solution which I like more as it gives a table from one expression.