Limited YTD line using Month Number

[hilmer101]

Hi,

I have an YTD line in a graph. I want it to be limited to February 2021, as I only have data until that point of time.

My DAX looks like this:

YTD 2021 Total Cost = CALCULATE (

sum('V-LOG Market KPI Overview'[Total Cost]) / SUM('V-LOG Market KPI Overview'[Sold M3]),

Filter(

ALL ( 'Fiscal Calendar' ),

'Fiscal Calendar'[Year] = 2021 &&

'Fiscal Calendar'[Month Number] <= MAX ( 'Fiscal Calendar'[Month Number] )

)

)

Thank you.

Best Regards,

Daniel

[jameszhang0805 · Accepted solution]

@hilmer101 Please try below solution 
Create a measure: 
This measure is used to determine whether the currently selected date is greater than the maximum date in the fact table, that is, whether the currently selected date is in the future.

ShowValueForDates = 
VAR _LastDateWithData =   -- calculate the maximum date in your fact table 
    CALCULATE (
        MAX ( 'MF_MB51'[Posting Date] ),
        REMOVEFILTERS ()
    )
VAR _FirstDateVisible =   
    MIN ( 'MD_Date'[Date] )
VAR _Result =
    _FirstDateVisible <= _LastDateWithData
RETURN
    _Result

 Then Put the measure in your YTD calculation.

MVTYTD.DYN = 
IF( 
    [ShowValueForDates] ,
        CALCULATE( [MVTQTY.DYN] , 
            DATESYTD( 'MD_Date'[Date] ) 
        )
)