Forum Discussion

hilmer101's avatar
hilmer101
New Member
5 years ago
Solved

Limited YTD line using Month Number

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's avatar
    jameszhang0805
    5 years ago

    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] ) 
        )
)



1 Reply

  • jameszhang0805's avatar
    jameszhang0805
    Icon for Resolver IV rankResolver IV
    5 years ago

    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] ) 
        )
)