Previous 3 weeks tickets created?

[JD0963]

Hello everyone, I hope someone can help me with this. Please see the attached chart. The axis is the weenum of the year (using a calendar table, linked with creationdate) and the values are counts of tickets created in the week number.

I want this to somehow say value of tickets created for the current week, plus previous 2 weeks. So the axis would have the current week (1) and the previous 2 weeks (52 and 51)

Is this at all possible? Also, would it be easy to have the week name instead? For example, my weeks are Monday to Sunday, would it be possible to have the axis to display the date of the monday instead of the week number?

Ideally it would look like the second chart? (sorry for the bad drawing)

Thank you for any help!!

current                                                                                            ideal

 

[Ashish_Mathur]

Hi,

Try this calculated field formula

=CALCULATE(COUNTA(Data[Ticket Number]),DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-14.MAX(Calendar[Date])))

Hope this helps.

[Smauro]

Hey there @JD0963

You have mentioned a calendar table. Is this table in Power Query?
If so, you could easily add a new column in the Power Query which will compute the Date each week started. Say that your day column is named [Day], then adding this using the Advanced Editor:

#"Inserted WeekStart" = Table.AddColumn(|YOURPREVIOUSSTEP|, "WeekStart", each Date.StartOfWeek([Day], Day.Monday), type date)
in
#"Inserted WeekStart"

will add a column with the date you're seeking. Alternatevely, you can add the column by selecting the [Day] and going Add column - > Date -> Week -> Start of Week. But the start of week will be Sunday. You'll then have to go on the step, click the gear and add the ", Day.Monday" after [Day].

On how it will be displayed on graphs, you can change it in Power BI Data view, selecting [WeekStart] and going Modeling -> Format etc.

Now, if you only want to display always the last three weeks, I believe you can simply do a visual filter on the chart you'll use WeekStart: Relative, is in the last [3] calendar weeks.


Hope that helps! 🙂

[JD0963]

Hi, thanks for your reply! My powerbi dashboard uses a tabular cube I have bult in SSAS - this is where the calendar table comes from! Is there any way of building what you described into my cube?

Sorry, I am quite new to this

Thank you

[v-shex-msft]

Hi @JD0963,

>> My powerbi dashboard uses a tabular cube I have bult in SSAS - this is where the calendar table comes from!

When you use live mode, current only as tabular mode support to add custom measure, other edit operations will be disabled in live mode.(in as cube mode, all edit feature will be disabled)

You can't enter to 'query editor' and add calculated tables in live mode.

Regards,

Xiaoxin Sheng