Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create table with repeating values based on start and end dates

I have a table with a number of records that have start a start-date and an end-date.  From this I'd like to create a second table that has a separate record that repeats a certain value for each mon...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous

     

    One way is to write a calculated table

     

    From the Modelling Tab>>New Table

     

    Calculated Table =
VAR temp =
    GENERATE (
        Table1,
        VAR no_of_month =
            DATEDIFF ( [Start], [End], MONTH ) + 1
        RETURN
            SELECTCOLUMNS ( GENERATESERIES ( 1, no_of_month ), "MyValues", [Value] )
    )
VAR temp2 =
    ADDCOLUMNS ( temp, "Month", EOMONTH ( [Start], [MyValues] - 1 ) )
RETURN
    SELECTCOLUMNS ( temp2, "ID", [ID], "Month", [Month], "Value", [Value] )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous

 

One way is to write a calculated table

 

From the Modelling Tab>>New Table

 

Calculated Table =
VAR temp =
    GENERATE (
        Table1,
        VAR no_of_month =
            DATEDIFF ( [Start], [End], MONTH ) + 1
        RETURN
            SELECTCOLUMNS ( GENERATESERIES ( 1, no_of_month ), "MyValues", [Value] )
    )
VAR temp2 =
    ADDCOLUMNS ( temp, "Month", EOMONTH ( [Start], [MyValues] - 1 ) )
RETURN
    SELECTCOLUMNS ( temp2, "ID", [ID], "Month", [Month], "Value", [Value] )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks - that works like a charm!  Appreciate the quick reponse as well. Cheers.