Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Graph deleting weeks without data

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

Need Help
  • az38's avatar
    az38
    6 years ago

    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

7 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • mussaenda's avatar
    mussaenda
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

     

    • az38's avatar
      az38
      Community Champion
      6 years ago

      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's avatar
      mussaenda
      Community Champion
      6 years ago

      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.