Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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.

View solution in original post

8 REPLIES 8
v-yulgu-msft
Employee
Employee

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

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.
Anonymous
Not applicable

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

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

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.
Anonymous
Not applicable

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

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

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

Anonymous
Not applicable

Hi Hi @v-yulgu-msft ,

 

Were you able to access th file?

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.