Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
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.
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
I think I have the date column in place, but I'm still having trouble with the drill down?
I also created the relationshipfrom date to my data points and it now looks like
Hi @Anonymous ,
Would you please share the sample .pbix file which can reproduce the same scenario for check?
Regards,
Yuliana Gu
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.
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |