Automatically select MTD/YTD as per respective visual level filters with in a report page

[dmuralli1829]

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

[amitchandak · Accepted solution]

@dmuralli1829 , 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

[amitchandak · Accepted solution]

@dmuralli1829 , 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

[dmuralli1829 · Accepted solution]

Thank you for the solutions, i did use first YTD but it wasnt working and when i used second YTD DAx, it worked

Regards

MD