Forum Discussion

TCR_Carlsberg's avatar
TCR_Carlsberg
Frequent Visitor
8 years ago
Solved

Create a new table based on start and end dates in my table

Hi All!

 

Will start by thanking you all on answering my previously questions! Big virtual clap on the shoulder.

 

The next problem I have is trying to create a graph over the numbers of campaigns per week.

This is my Campaign table:

As you can see I dont have one line per week just the start week and end week.

With this data I like to create a graph to compare the 2 years to eachother, like this:

To create this graph I faked the result table I need help with. The table I created by hand looks like this:

 

So does anyone of you have and idee how I can create my helper table from a combination of my Campaign table and mayby an Calender table?

Is there someway I can create the graph without my helper table just by creating measuers/columns?

 

I have used SUMMARIZE before to create tables but in this case I need multiple lines per week line and I cant get that with SUMMARIZE as I see it.

 

Kind regards,

Thomas

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi TCR_Carlsberg

     

    Try this

     

    Go to Modelling Tab>>>> NEW TABLE and enter this formula

     

    NEW TABLE =
SELECTCOLUMNS (
    GENERATE (
        Campaign_Table,
        GENERATESERIES (
            CALCULATE ( VALUES ( Campaign_Table[StartWeek] ) ),
            CALCULATE ( VALUES ( Campaign_Table[EndWeek] ) )
        )
    ),
    "CampID", Campaign_Table[CampID],
    "CampName", Campaign_Table[CampName],
    "Year", Campaign_Table[Year],
    "Week", [Value]
)

     

6 Replies

  • McCow's avatar
    McCow
    Icon for Resolver III rankResolver III
    8 years ago

    Hi TCR_Carlsberg

    Are you alredy tryed CALENDAR function? Example:

    Table = CALENDAR(DATE(2015;11;1);now())

    Best regs

    • TCR_Carlsberg's avatar
      TCR_Carlsberg
      Frequent Visitor
      8 years ago

      I dont have any trouble creating a calender. I just cant use that to create a helper table.

      This is how I created the Calender table I played with to try and solv this by myself:

      Calendar = ADDCOLUMNS ( CALENDAR (DATE(2016;1;1); DATE(2018;12;31)); 
    "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" ); 
    "Year"; YEAR ( [Date] ); 
    "MonthNo"; FORMAT ( [Date]; "MM" ); 
    "YearMonthNo"; FORMAT ( [Date]; "YYYYMM" ); 
    "YearWeek"; FORMAT ( [Date]; "YYYY" )&FORMAT(WEEKNUM([Date];2);"0#"); 
    "WeekNo"; FORMAT(WEEKNUM([Date];2);"0#"); 
    "Month"; FORMAT ( [Date]; "mmmm" ); 
    "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); 
    "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ))

       

      Kind regards,

      Thomas

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Hi TCR_Carlsberg

         

        Try this

         

        Go to Modelling Tab>>>> NEW TABLE and enter this formula

         

        NEW TABLE =
SELECTCOLUMNS (
    GENERATE (
        Campaign_Table,
        GENERATESERIES (
            CALCULATE ( VALUES ( Campaign_Table[StartWeek] ) ),
            CALCULATE ( VALUES ( Campaign_Table[EndWeek] ) )
        )
    ),
    "CampID", Campaign_Table[CampID],
    "CampName", Campaign_Table[CampName],
    "Year", Campaign_Table[Year],
    "Week", [Value]
)