Show categories with no data on a chart

[OlegV]

Hello, 

I need to recreate an Excel chart in PowerBI. I need to show dates without data (but excluding weekends.)

But in PowerBI I get this result:

Can you please help me.  

[V-pazhen-msft · Accepted solution]

@OlegV 

You can create a new date column and use the new dates in the table visual and filter.

New Date =If(weekday([date],2)<=5, [date], Blank())

You might also need weekdays by create following 2 columns.

Weekday = if([New Date]<=5, [New Date] , Blank())

Text weekday = Switch([Weekday], 
1, "Mon",
2, “Tue",

3, "Wed",
4, "Thur",

5,"Fri",
Blank())

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[OlegV]

Thank you,
so as to show dates without data:
1) Create a Calendar table
2) Create a column with dates excluding Weekends using a function: DateNoWeekends =If(weekday('Calendar'[DateKey],2)<=5, 'Calendar'[DateKey], Blank())
3) Create a column with WeekDayNames using a function:  WeekdayDayName = 'Calendar'[DateKey] and format it as "ddd"

4) Plot WeekdayDayName  on x-axis, numerial values on y-axis and filter a visual with DateNoWeekends.

[amitchandak]

@OlegV , You need a date table for that.

You can add +0 then it will show all the dates

You need to create measure like

0 between range
Measure = var _1= [Price MT] +0 // assume you have a measure [Price MT] 
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date])  &&'Calendar'[Weekday] =1  >_max , BLANK(), _1))

column in calendar

Weekday = if( WEEKDAY([Date],2) >6,0,1)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[OlegV]

@amitchandak Thank you for your reply.
It shows a mistake:

I did not mention, I have a page filter where I choose the dates for which to show the data.