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 

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
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 

 

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

    Greg_Deckler ,
    Is there a way to do not hardcode dates in expression?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Sure, if you have dates in your data, you can use MIN and MAX or use CALENDARAUTO but not sure what your data looks like. If you have posted the sum total of your data, you will need to parse out the beginning and ending dates of your periods.

       

      Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

      The most important parts are:
      1. Sample data as text, use the table tool in the editing bar
      2. Expected output from sample data
      3. Explanation in words of how to get from 1. to 2.