Forum Discussion
Automatically select MTD/YTD as per respective visual level filters with in a report page
Hi I am facing an issue while selecting filters in visual in a report page.
1) I have visual which displays MTD sales
2) I have visual which displays YTD sales
When i slect date for page level both visual will list only MTD sales.
But i want when i select Apr 2021, the MTD visual should display ony for APR 2021(Currently it does) and YTD visuals should display sales from Jan to Apr 2021 but when i select Month -Year filter at page level it display sales values for Apr 2021 in YTD not a running total, how to display running YTD values when i selected Apr 2021 in page level filters
Can you please help
Regards
MD
Anonymous , Can share with you MTD, YTD measures. If you use time intelligence, then the measure can show MTD and YTD. But when you select YTD with a date say April. Only YTD of April will be shown, you will not get the trend for 4 months.
example measure
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
But if you want a trend of 4 months, or any range more than selected as a trend you need an independent date table
You have select month from independent table and try a measure like
YTD Sales =
var _min = minx(allselected(Date1), date1[Date])
var _max = maxx(allselected(Date1), date1[Date])
return
CALCULATE(CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")), filter('Date'[Date], 'Date'[Date] >=_min && 'Date'[Date] <=_max))Refer my video for more details
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
2 Replies
Anonymous , Can share with you MTD, YTD measures. If you use time intelligence, then the measure can show MTD and YTD. But when you select YTD with a date say April. Only YTD of April will be shown, you will not get the trend for 4 months.
example measure
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
But if you want a trend of 4 months, or any range more than selected as a trend you need an independent date table
You have select month from independent table and try a measure like
YTD Sales =
var _min = minx(allselected(Date1), date1[Date])
var _max = maxx(allselected(Date1), date1[Date])
return
CALCULATE(CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")), filter('Date'[Date], 'Date'[Date] >=_min && 'Date'[Date] <=_max))Refer my video for more details
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI