Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Date X Axis

Good Morning!

 

I've been struggling to figure out how to get an X-Axis for dates set up for reporting and was looking for help trying to create reports using a proper X-Axis for dates.

 

One of my reports -

 

I do use a date slicer and it works well.

but i will get charts like this

And to be fair to the logic i do have it filtered by month since I haven't been able to figure a proper way to get it to filter correctly.

 

and a sample of my date data row I'm working with.

 

 

This is a file with all the dates in one file. A side question leading from this is also, if I have multi files such as these (say for different groups/topics/etc) how do you do the axis then? I currently haven't firgured this out, so I essentially have just.. bars

 

Any would be appreciated! I'm still pretty novice at powerbi so examples are always appreciated!

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous ,

     

    Please refer to below measures.

    count Test1 =
CALCULATE (
    COUNT ( 'Sample Data'[Test1] ),
    FILTER (
        ALLSELECTED ( 'Sample Data' ),
        'Sample Data'[Test1].[Date] >= MIN ( 'Date'[Date] )
            && 'Sample Data'[Test1] <= MAX ( 'Date'[Date] )
            && 'Sample Data'[Test1].[Year] = SELECTEDVALUE ( 'Date'[Date].[Year] )
            && 'Sample Data'[Test1].[Month] = SELECTEDVALUE ( 'Date'[Date].[Month] )
    )
)

count Test2 =
CALCULATE (
    COUNT ( 'Sample Data'[Test2] ),
    FILTER (
        ALLSELECTED ( 'Sample Data' ),
        'Sample Data'[Test2] >= MIN ( 'Date'[Date] )
            && 'Sample Data'[Test2] <= MAX ( 'Date'[Date] )
            && 'Sample Data'[Test2].[Year] = SELECTEDVALUE ( 'Date'[Date].[Year] )
            && 'Sample Data'[Test2].[Month] = SELECTEDVALUE ( 'Date'[Date].[Month] )
    )
)

    I have uploaded the .pbix file.

     

    Best regards,

    Yuliana Gu

8 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    This is a file with all the dates in one file. A side question leading from this is also, if I have multi files such as these (say for different groups/topics/etc) how do you do the axis then? I currently haven't firgured this out, so I essentially have just.. bars

     

    What is your desired output? Would you please illustrate your requirement with examples?

     

    Regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Yuliana!

       

      Thank you for responding, I'm hoping for a display like this below with the month/year that works with the relative time slicer I have.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous ,

         

        I would suggest you create a calendar table which is linked to the fact data table based on common filed [Date]. Add [Date] from calendar table into slicer and into X-axis of column chart. Click the drill down icon to drill the hierarchy to "Year-Month" level in chart visual.

         

        Best regards,

        Yuliana Gu