Forum Discussion
Dynamic axis change based on custom date slicer selection
Hi Anonymous,
These formula only calculate fixed date range, I don't think you can direct use last 90 days as last three month, it can't mapping the correct data range.
For your scenario, you can try to use below formula, I use date function to calculate previous date to replace static number of days:
DatePeriod =
VAR TD =
TODAY ()
RETURN
UNION (
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE ( 'Date', DATESBETWEEN ( 'Date'[Date], TD - 07 + 1, TD ) ),
'Date'[Date]
),
"Period", "Last 07 Days"
),
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE ( 'Date', DATESBETWEEN ( 'Date'[Date], TD - 14 + 1, TD ) ),
'Date'[Date]
),
"Period", "Last 14 Days"
),
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE (
'Date',
DATESBETWEEN (
'Date'[Date],
DATE ( YEAR ( TD ), MONTH ( TD ) - 1, DAY ( TD ) ),
TD
)
),
'Date'[Date]
),
"Period", "Last Month"
),
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE (
'Date',
DATESBETWEEN (
'Date'[Date],
DATE ( YEAR ( TD ), MONTH ( TD ) - 3, DAY ( TD ) ),
TD
)
),
'Date'[Date]
),
"Period", "Last three month"
),
ADDCOLUMNS (
SUMMARIZE ( CALCULATETABLE ( 'Date' ), 'Date'[Date] ),
"Period", "Overall"
)
)
Regards,
Xiaoxin Sheng
Anonymous, thanks for the solution, however I don't think it provides what I am after. On the chart below you can see that the last three months are still shown as days rather than months. I would like to see three charts for july, august & sept rather than all the days. Any ideas if that is doable?
Hi Anonymous,
You can modify your date column to hierarchy mode, then remove quarter and day fields and drill to bottom level. After these steps, your graph should summary by year month level.
If above not help, please share some sample data for test.
Regards,
Xiaoxin Sheng