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

[TCR_Carlsberg]

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

[McCow]

Hi @TCR_Carlsberg

Are you alredy tryed CALENDAR function? Example:

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

Best regs

[TCR_Carlsberg]

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 · Accepted solution]

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]
)

[TCR_Carlsberg]

That is a beautiful solution. Works like a charm in my test case. I'll try it on my complete dataset...

Thank you!

/T

[Zubair_Muhammad]

@TCR_Carlsberg

[Zubair_Muhammad]

@TCR_Carlsberg

Please see the attached file here

(With your sample data)