Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Help with displaying all the months in X Axis even with no data available

  • 1.  Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 02, 2020 04:23 PM
    Hello

    I have tried to select "Show Data with no data but it still doesn't show all the months. I would like to show all the months for 2020 and it would end in 2021. This fiscal would show data from April 2020 - March 2021. Below is a view and it only shows two months but I would like the dates to display even with no values in the columns. If someone could help me, please. I tried to google the problem but I couldn't find a solution.



    ------------------------------
    Thank you kindly,
    Joanne

    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Help with displaying all the months in X Axis even with no data available

    Top Contributor
    Posted Jun 02, 2020 08:20 PM
    you need to link your data to a dates table and then use the month field from that dates table (NOT from your data table) as the X axis.

    Think of it as manipulating the join type.

    ------------------------------
    Lutz
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 03, 2020 04:56 AM
    Edited by Ben Howard Jun 03, 2020 04:57 AM
      |   view attached
    From the image, it does look like the date is being used from the date table.  I tried this also, and was successful, so attached is the file and you might spot something to help you.


    ------------------------------
    Ben Howard
    ------------------------------

    Attachment(s)

    pbix
    PBIUG1.pbix   113K 1 version
    Academy - Online Interactive Learning from Experts


  • 4.  RE: Help with displaying all the months in X Axis even with no data available

    Posted Jun 03, 2020 11:16 AM
    Hi Joanne,
    I've had the same issue in the past. Make sure your dates on the x-axis are set as categorical and not continuous. I actually create 2 columns in my date (calendar) table to solve this problem. (You will also need columns in your date table for month number and year.)

    Use this categorical text column on the x axis: MMM-YYYY = FORMAT([Date],"MMM-YYYY")

    Sort the above column by this column to ensure correct date order on your graph:

    YYYYMM = 'D_Calendar'[Year]&IF(LEN('D_Calendar'[Month Number])=1,"0"&'D_Calendar'[Month Number],'D_Calendar'[Month Number])

    In case you need these:
    Month Number = MONTH([Date])
    Year = YEAR([Date])

    Best of luck!
    Krista


    ------------------------------
    Krista Birch
    R&D Business Intelligence Data Analyst
    Abbott Park IL
    2246680247
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 03, 2020 12:33 PM
    Hi Krista,

    Thank you so much for taking the time to help me. I am new so I really appreciate any help!

    I added these calculated columns to my dates table. I must be still doing something wrong because It didn't change anything to help reflect the other months.  I'm also not sure how to change this as you suggest "Make sure your dates on the x-axis are set as categorical and not continuous." Where do I find this as I do not see this option under the X option under the Format section?

    I might not have my tables connected correctly I will attach the diagram view of that.



    ------------------------------
    Joanne Osborne
    Data Analyst
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Help with displaying all the months in X Axis even with no data available

    Posted Jun 03, 2020 01:05 PM
    Hi Joann,
    Please try checking your column settings for MMM-YYYY and also make sure you have "show items with no data" checked?



    ------------------------------
    Krista Birch
    R&D Business Intelligence Data Analyst
    Abbott Park IL
    2246680247
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 03, 2020 05:48 PM
    Hi Joanne - Lots of good advice above.  I too ran across this problem.  If you want the data to show up, there has to be something on which to report, even if it's zero.

    Consequently, you might try ensuring your data has values for the periods missing.  Try keeping records in these months and replacing any "null" values with 0 (Zeros).  Your missing dates then show up because it's reporting (Zero) for the months where there are no hours or cost.

    I have found the "show items with missing data" to be somewhat frustrating as it then shows ALL dates in your date table.  This is probably what you do not want.  You just want the dates BETWEEN the start and end with blanks to show up.  Adding the zeros for blank data will solve this issue.

    Guy-In-A-Cube with Marco Rusio covered how to stop "Future" dates from showing up in cumulative data in a nice video here:

    https://youtu.be/VXd1wl-5vKI

    Best of luck...

    ------------------------------
    John Thomas
    Huntersville NC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 03, 2020 06:15 PM
    Hi John, Great advice as you say, but if you check out my file attached in my previous post, you'll see that you do not need data (even if it is null) for each column on the report.  My data clearly has months in the date table, but no values in the associated values table.  I think the issue here is that people's data, models and even visualisations are set up differently, leading to  different results

    ------------------------------
    Ben Howard
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 04, 2020 07:30 AM
    Hi Everyone,

    Another interesting challenge.

    Ben I used your calendar table as a 2nd table in my model.

    It works great thank you so much... My dilemma now is to show the tabs on the X Axis per my slicer above by Fiscal 2019-2020 and 2020-2021

    My fiscal period runs April 1 2019 - March 30 - 2020 and April 1st 2020 - March 31, 2021

    It would be nice to show the tabs for these months below when I click on the slicer.

    This is what I currently am seeing. When I click on 2019-2020 it would be nice to only show this complete fiscal year




    Then when I click on the 2020-2021 It would be nice to show the completed x axis with April 2020 - March 2021

    I like have the slicer to switch between fiscal years but I'm not sure if I  would have to write a Dax code to hide the months and years that don't belong in each fiscal year? Do you think this is something that can be managed by the filters for the slicer? I'm just not sure...




    ------------------------------
    Thanks
    Joanne Osborne
    Power bi Newbie
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 04, 2020 08:13 AM
    You'll need to define your fiscal year, and I would do that as additional m-code that you already have for the calendar. For each date, determine the fiscal year, you'll need an if statement to do this.

    ------------------------------
    Ben Howard
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 04, 2020 08:41 AM
    Thanks, Ben

    Yes, your instructions on this worked perfectly. I am now able to add a slicer for the two fiscals and report only the months for those fiscal in my visuals.

    Your teachings and everyone else is priceless...I hope that someday I can help others the way that you all helped me.

    Have a great day and take care.

    Thank you, Thank you, Thank you...
    Joanne

    ------------------------------
    Joanne Osborne
    Data Analyst
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted 30 days ago
    @Ben Howard - I am getting mixed results with your sample file.  If using the date table and changing the X-Axis values from Month-Year, to Week End, or even Week Year it's dropping all the blank or null values.  I noticed in your "Measure" you added "... + 0", which forces the value to be zero even when it's null, which is the same approach I was advocating and eliminates zero values from the fact table, which is nice.

    I may be doing something wrong, but Joanne seems to be happy so at the end of the day that's what counts.

    Thanks,​

    ------------------------------
    John Thomas
    Huntersville NC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted 30 days ago
      |   view attached
    Hi John, you have to reset the "show blank values".

    Re the measure, for sure, sometimes I do this when using cards so as not to show "blank", but this measure isn't required in this case.


    ------------------------------
    Ben Howard
    ------------------------------

    Attachment(s)

    pbix
    PBIUG1.pbix   114K 1 version
    Academy - Online Interactive Learning from Experts


  • 14.  RE: Help with displaying all the months in X Axis even with no data available

    Posted Jun 03, 2020 01:02 PM
    Hi Joanne,
    Try this below to figure out where could be the issue.
    Copy chart visual and add new page paste it. Change to Table to see what is happening.(If still can't figure out, try to remove Teams column and check*)
    Make sure you clear all filters to that tables. You can check highlighting that table and see Filters tab. Sometimes filters should be applied on that table without knowing.
    Once you figure out, you can bring back.

    ------------------------------
    Pulendar vadde
    IT consultant
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 15.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 04, 2020 06:48 AM
    Hi Everyone

    Thank you so much and I think I understand why this is happening. My dim date table that I'm currently using is a dates table that I found on the Internet and it's great has all the columns and it's also in  PBIT format. However it the end date is today's end date it grows automatically without me having to put an end date which is great but for this circumstance, it's not allowing me to add all the additional months because it stops on June 4 so that's all it sees. Ben your calendar date is great and I was even able to change the end date to March 30th, 2021. I'm just not sure how to copy your calendar table into my PBIX file. Is that even possible to do? I would still keep my original calendar but would like to use Ben's or a new calendar that has an end date of March 30 2021 just for the X-axis just to create the missing months.

    Sorry, I'm slow in understanding what everyone has been telling me along. It's my fault for not explaining to you all the calendar that I'm using stops today. I'm learning so much from you all and really do appreciate everyone trying to help me.

    Have a great day and take care and please stay safe.

    Thanks
    Joanne

    ------------------------------
    Joanne Osborne
    Data Analyst
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 16.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted Jun 04, 2020 07:29 AM
    Joanne, you can open the query editor (transform data) and select the date query, open the advanced edit, and copy the m-code.  From there, open your file, create a new blank query, and then from the advanced editor, pasted in the m-code.  You'll be asked for start/finish dates for the calendar.  Then create the relationships between the date table and the other tables in your model.

    ------------------------------
    Ben Howard
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 17.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted 30 days ago
    Edited by John Thomas 30 days ago
    Joanne,

    You can right-click on the name of the date table in the query pane and then right-click in your existing pbix query pane and select paste.  Not only will the "DateTable" query be copied, but also the Query function Ben has will be pasted too as it is a dependent query.

    Copy DateTable with Right-Click and select copy:
    Right-Click and select copy.  Be sure you are coping the DateTable query.

    Right-Click in new file and select "Paste":
    Right-Click and select Paste to paste the query and all it's dependents.


    ------------------------------
    John Thomas
    Huntersville NC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 18.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted 30 days ago
    Thanks, John

    That is great for me to know. I have always cut and pasted the code into a new query through Advanced editor. I didn't know there was any other way.

    ------------------------------
    Joanne Osborne
    Data Analyst
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 19.  RE: Help with displaying all the months in X Axis even with no data available

    Gold Contributor
    Posted 30 days ago
    Yes - I use to do it that way too, but this is much better.  If you put your queries in folders, you can copy at the folder level too!  It will copy the folder and all the queries in it.  Much faster than opening the Advanced Editor and selecting everything, then pasting it into a blank query.

    Glad it worked out for you...

    Thanks,


    ------------------------------
    John Thomas
    Huntersville NC
    ------------------------------

    Academy - Online Interactive Learning from Experts