Hide Future Months YTD Calc

[Anonymous]

Hi All,

I have a slicer from my Date Dim table - it pulls Fiscal Month and Year. I want to visualise monthly trend on a line chart, however, for any month selected on the slicer, i want to make sure the full year is still showing. 

To do this, i brought in 'Month End' field from my Fact table. This all works great. When i select a fiscal month, i can still see 12 months of data on my axis.  What i need now is to to 'Actuals' for the selected month as well as prior months (i.e. hide future months). 

I'm using the following to get YTD Actuals: 

TOTALYTD(SUM(Data[Amount]), Data[Month End], Data[Cost] = "Actual")

This gives me YTD from the month i select in the slicer all the way till Dec2021 which is incorrect. 

When i select a Fiscal Month, i want my Actual YTD line to start from the beginning of the fiscal year (June) and end at the month selectred. I.e. if i select Aug21, i want my Actual YTD line to start from June21 and end at Aug21. 

How can i amend my formula above to account for this? 

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

You need to calculate YTD base on the selected date instead of today?

Firstly , create a new table for slicer :

Slicer table =  VALUES('Data'[Month End])

Try some measures like the following: the logic is use blank() to hide the month you do not want to display.

YTD =
VAR _1 =
    TOTALYTD(
        SUM( Data[Amount] ),
        ALL( Data[Date] ),
        Data[Cost] = "Actual",
        "6/21"
    )
VAR _sd =
    MAX( 'Data'[Date] )
VAR _ss =
    MAX( 'Slicer table'[Month End] )
RETURN
    IF( _ss <= _sd, BLANK(), _1 )

I am not sure the result of the TOTALYTD you provided is the right result you want. i provide another measure to calculate the sum. if it incorrectly ,please share some example data. I put my pbix file in the attachment you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cinthya_Darci]

This work also for me when using quick measure

Pipeline Generated YTD = 
VAR t = IF(
	ISFILTERED('Pipeline Generated'[Snapshot Date End]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	TOTALYTD(
		[Pipeline Generated],
		'Pipeline Generated'[Snapshot Date End].[Date]
	)
)
VAR sd = MAX ( 'Pipeline Generated'[Snapshot Date End] )
VAR ss =  MAX ('Pipeline Generated'[Today] )
RETURN IF ( ss <= sd , BLANK() , t )

[Cinthya_Darci]

This works for me for a quick measure too! Thanks!

[lbendlin]

Add a filter that requires the date to be less or equal to TODAY()

[Cinthya_Darci]

This doesn't apply necessarily, if the table you are working on has only months with data, future months are created by the calculation, when trying this approach it will always retrieve TRUE.