Always show most recent date in chart without filter how to?

[Anonymous]

Is there a way to always show the most recent date of metrics in a chart without selecting a filter date in any way? 

For example, if I were to open the pbix file and click refresh, only data for September 5th would show. I have a few formulas for metrics within the chart if I could modify those. I do not want to manually select September 5th in anyway. 

I have a couple of rolling averages and percent change calcs as below. 

% Change from 12 Week to Current Month = 
VAR __BASELINE_VALUE = 'LNApps_Facts'[Amount 12 Week Rolling Average]
VAR __VALUE_TO_COMPARE = 'LNApps_Facts'[Last 4 Weeks]
RETURN
	IF(
		NOT ISBLANK(__VALUE_TO_COMPARE),
		DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
	)

Rolling Average = 
IF(
	ISFILTERED('LNApps_DateDim'[ActualDate]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
	VAR __LAST_DATE = LASTDATE('LNApps_DateDim'[ActualDate].[Date])
	RETURN
		AVERAGEX(
			DATESBETWEEN(
				'LNApps_DateDim'[ActualDate].[Date],
				DATEADD(__LAST_DATE, -21, DAY),
				DATEADD(__LAST_DATE, 0, DAY)
			),
			CALCULATE(SUM('LNApps_Facts'[Amount]))
		)
)

[Anonymous]

I just found the relative date filtering to filter just the day, but this only works without using a date hierarchy. If I try to do this, my chart goes blank and displays this error: "Time intelligence quick measures can only be grouped or filtered by the power BI-provided date heirarchy."

[v-huizhn-msft]

Hi @Anonymous,

Could you please share your .pbix file for further analysis? So that we can test and reproduce your scenario.

Best Regards,
Angelia

[Anonymous]

I cannot share a file all the data is too confidential. I just am confused about how to filter a time intelligence measure such as a rolling average using the relative date filter in the sidebar pane. Sidenote I don't know if this helps but the time intelligence measures were made using Quick Measures

[Anonymous]

https://community.powerbi.com/t5/Desktop/Page-Level-Filter-NOW-1/td-p/8257

This thread was helpful, but I'm still stuck I believe due to the date my chart is referencing being a date hierarchy. 

IsCurrentday =
IF ( 'Table'[Date] = TODAY () - 1, "Yes", "No" )

I tried using the formula above to insert into the visualization filter to make the chart always show the most recent date - which for my purposes I need to be the day before today. This is not working either and I am thinking it is because the formula is referencing the non-date hierarchy version of my actualdate field. 

Would something like this (it's incorrect) fix my issue with the formula above to always get the most recent day before today?

I do not have a "day " field like "month" or "year" instead all I have is "actualdate" which reads like "Friday, September 8, 2017. 

IsCurrentday = 

IF ( LNApps_FundingDateDim[month] = MONTH( 
&& LNApps_FundingDateDim[year] = year( 
&& LNApps_FundingDateDim[ActualDate] = day( - 1, "Yes", "No" )

[v-huizhn-msft]

Hi @Anonymous,

Actually, I am confused with your sentense "I do not have a "day " field like "month" or "year" instead all I have is "actualdate" which reads like "Friday, September 8, 2017. "? Your actualdate has different format?

Best Regards,
Angelia