Newbie question (I think): how to get the correct date range?

[BPTBPTBPT]

Hello, I am making a graph that shows how many documents are created per day in the last 365 days. So, on the X-axis, I would like to view 365 days: from today (or approximately today) to a year back.

Please see the screenshot.  For some reason I only get a range of 31 days, and I think that all values are added up per day. So day 1 on the graph is the added value of 1-4-22 + 1-3-22 + 1-2-22 [...] + 1-5-21.

How do I get what I want?

Bonus question: is there a quick way to filter out all saturdays and sundays?

[v-jayw-msft · Accepted solution]

Hi @BPTBPTBPT ,

For the x-axis of the chart visual, use date hierarchy-year-month-day instead of using date hierarchy-day.

Best Regards,

Jay

[BPTBPTBPT]

Thank you, Sevenhills, 

Indeed I am looking for an x-axis that shows day 1,2,3,4,5 (or preferably: 3-5-2021, 4-5-2021, 5-5-2021, ... 3-5-2022).

About the date table: I looked into it, but I am not sure how to apply the date table (once I got it) to my report. What do I do with the date table when it is ready? Also I did not see ways to eliminate saturdays and sundays in these links? 

Edit: wouldnt a date table make the report rather "stiff"? I could try and make one for 3-5-2021 to 3-5-2022, but when I want to view my report in a month or so, do I have to make a new date table (from 3-6-2021 to 3-6-2022)?

[sevenhills]

Please post some mockup data and expected chart. This helps to look into solution. 

[sevenhills]

Looks like, the chart is showing day 1 to Day 31 of every month you selected. Do you want to show on axis is the date? Day 1, 2, 3, 4 ... Day 365?

Also, to exclude weekend and show only weekdays, I will recommend to build a date table.

You can get the same behaviour i.e., without date table in DAX.

Pls.check:

https://www.mssqltips.com/sqlservertip/6756/power-bi-calendar-table/

https://gorilla.bi/power-query/date-table/ 

https://radacad.com/power-bi-date-or-calendar-table-best-method-dax-or-power-query