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
Solved! Go to Solution.
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] )
Are you alredy tryed CALENDAR function? Example:
Table = CALENDAR(DATE(2015;11;1);now())
Best regs
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
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] )
That is a beautiful solution. Works like a charm in my test case. I'll try it on my complete dataset...
Thank you!
/T
User | Count |
---|---|
141 | |
60 | |
58 | |
58 | |
47 |
User | Count |
---|---|
135 | |
73 | |
58 | |
56 | |
51 |