cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Blue714 Frequent Visitor
Frequent Visitor

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.

Slicer.JPG

but i will get charts like this

Chart.JPG

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.

filter.JPG

 

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

 

 

data date row.JPG

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

bars.JPG

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Date X Axis

Hi @Blue714 ,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support Team
Community Support Team

Re: Date X Axis

Hi @Blue714,

 


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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Blue714 Frequent Visitor
Frequent Visitor

Re: Date X Axis

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.

Month Year.png

Highlighted
Community Support Team
Community Support Team

Re: Date X Axis

Hi @Blue714 ,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Blue714 Frequent Visitor
Frequent Visitor

Re: Date X Axis

I think I have the date column in place, but I'm still having trouble with the drill down?

Date.JPG

 

I also created the relationshipfrom date to my data points and it now looks like

Newchart.JPG

Community Support Team
Community Support Team

Re: Date X Axis

Hi @Blue714 ,

 

Would you please share the sample .pbix file which can reproduce the same scenario for check?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Blue714 Frequent Visitor
Frequent Visitor

Re: Date X Axis

Hi @v-yulgu-msft ,

 

Thank you for looking into this, I've added a sample .pbix i recreated similar to what I am working with.

Sample.pbix

Blue714 Frequent Visitor
Frequent Visitor

Re: Date X Axis

Hi Hi @v-yulgu-msft ,

 

Were you able to access th file?

Community Support Team
Community Support Team

Re: Date X Axis

Hi @Blue714 ,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.