Force the end of the YTD on certain measure

[Anonymous]

Hi everyone,

I have a model with two fact table and a calendar. 

FactTable1 goes until 31/12/2021

FactTable2 goes until 31/08/2021

The calendar goes until 31/12/2021

I'm calculating some YTD value (with DATESYTD(Calendar[Date]) ) on FactTable2 but by default the YTD is calculated on 31/12/2021, so if i want the real value i have to use a slicer and filter on August which is not optimal in my case. 

I wanted to force the YTD to the last date registered in FactTable2 for measure which read FactTable2 and not to the lastdate of the Calendar.

Something like this but which is working :

RealMaxDate = lastdate(FactTable2[Date])

YTD = CALCULATE([Amount],DATESYTD(Calendar[Date]),FILTER(Calendar,Calendar[Date]<=RealMaxDate))

Thank you very much,

Best regards

[Anonymous]

It's working Jihwan_Kim, thank you 😉

In the mean time i managed to find another solution which was working im my case two if someone is interested :

In my calendar table i added two column :

LastDateFactTable1 = Calendar[Date] <= max(FactTable1[Date])

LastDateFactTable2 = Calendar[Date] <= max(FactTable2[Date])

Then in my YTD measure i use those column as a filter, like this :

MeasureFact1 YTD = CALCULATE([MeasureFact1],CALCULATETABLE(DATESYTD(Calendar[Date],Calendar[LastDateFactTable1]=TRUE))

MeasureFact2 YTD = CALCULATE([MeasureFact2],CALCULATETABLE(DATESYTD(Calendar[Date],Calendar[LastDateFactTable2]=TRUE))

[Jihwan_Kim · Accepted solution]

Hi,

I think, one of the ways to create it is to use KEEPFILTERS function.

Qty two YTD fix : =

VAR twomaxdate =

MAXX ( FILTER ( ALL ( Sales_two ), Sales_two[Qty] <> 0 ), Sales_two[Date] )

RETURN

CALCULATE (

[Qty two YTD :],

KEEPFILTERS ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= twomaxdate ) )

)

Link to the sample pbix file