Graph deleting weeks without data

[Anonymous]

Re: End of Week DAX for non standard workweek: Mon - Sun

Thursday

Is there a method to display weeks if no data exists for that week?  I need to show a bar graph with totals by week - which this formula does beautifully - but in a filtered four week period, if one week does not have data it does not generate a "0" bar  as a place holder for that week.  It simply omits that week.

How can I ensure that even weeks without data still show up as a "0" bar as a placeholder for that week?

Week1Dates = 25  Week2Dates = 13  Week3Dates=0  Week4Dates=32   Week3 will be omitted in the bar graph and not show as a "0" with this formula.

Here is the foumla I am using.  Works great except for that one issue.

EOWeekMoSu = [Date] - MOD([Date]-2,7)+6

Thanks!  

Signed,

Dave in MEM

[Anonymous]

Is there a method to display weeks if no data exists for that week?  I need to show a bar graph with totals by week - which this formula does beautifully - but in a filtered four week period, if one week does not have data it does not generate a "0" bar  as a place holder for that week.  It simply omits that week.

How can I ensure that even weeks without data still show up as a "0" bar as a placeholder for that week?

Week1Dates = 25  Week2Dates = 13  Week3Dates=0  Week4Dates=32   Week3 will be omitted in the bar graph and not show as a "0" with this formula.

Here is the foumla I am using.  Works great except for that one issue.

EOWeekMoSu = [Date] - MOD([Date]-2,7)+6

Thanks!  

Signed,

Dave in MEM

[Anonymous]

This example shows Dec 22 bar graph missing as no work orders were closed in that week.  I would like a "0" bar graph legend/bar to show as a placeholder for that week.

[mussaenda]

Okay, now I understand.

What you need is a calendar table and use that on your calculation.

For sure this way you will solve your problem.

[az38]

@Anonymous 

did you try to create a calendar table?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[mussaenda]

Can you try putting 0 at the end of your formula?

If this does not solve the problem, try showing "Show items with no data" on your column using right click on your field.

[Anonymous]

I tried your possible solutions with no luck.  I really appreciate your response and will try several variations of your solution.  The field I have that is causing problems has one week of 4 that are being evaluated with no data.  Hence it is not showing at all on the graph.  I will keep trying ...thank you again!

[az38 · Accepted solution]

Hi @Anonymous 

the best practice to tasks like your is to create dimension calendar table. like

Calendar Table = CALENDARAUTO()

then you need to add a calculated column in this Calendar Table

Week = FORMAT('Calendar Table'[Date], "YYYY-WW")

then create relationships 'Calendar Table'[Date] - [Date] 

then add 'Calendar Table'[Week] as Axis value

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

I will have to work through this as I have not tried this before.  Thank you for the idea and it will likely make future iterations of date-related charts easier.  Thank you for your reply!  Dave in Memphis.