Ignore Weekend in Forecast

[AhmedT001]

Hi,

I've created a Line Chart with a Forecast using some data that is pulled out directly from our IBM dB2 Database. The image below is a snippet of the line chart, with sensitive data blocked out. 

However this line chart takes into account weekends, where there is no activity, hence the straight horizontal lines at the bottom. Within my Dataset, we have Invoices against the date they are done, in the format of DD/MM/YYYY. 

How do I go about only using Weekdays in my Forecast/Line chart? I gather I need to do something in PowerQuery and create a custom column or something but beyond that, I don't really know what to do.

Can anyone help me please? 

Thanks for any help. 

[AhmedT001 · Accepted solution]

I managed to figure it out by doing lots of reading/youtube/googling the fix was as follows.

1) Go to Transform Data.

2) In the Transcation Table, click on Add Column, then Custom Column

3) Give it a name, say Weekend for example.

4) In the Custom Column Formula section, input the following (without the quotes):

"if Date.DayOfWeek([INVOICE_DATE], Day.Monday) > 4
then
"Weekend"
else
"Weekday""

5) Click Ok

6) Click Close&Apply

7) Add new Weekend field as a Filter on this page

😎Select Weekday

9) Problem solved :). 

[mickey64]

For your reference.

"Filter pane"

[AhmedT001]

This didn't work as it ended up removing the forecast. I've posted what the fix was, for me anyway. 

[AnkitKukreja]

Hi! @AhmedT001 

I think you are on right track, if you can have an identifier for weekend then you can exclude that from your visual by using page level/ visual level filter to avoid weekends, hope this helps, if not please share the sample data to reproduce the same.

[AhmedT001]

Yes this is the route I had to go down, I've posted my solution as well.