Forum Discussion

Del235's avatar
Del235
Icon for Helper III rankHelper III
1 year ago
Solved

Grand Total thru Today()

What would be the syntax for a grand total thru Today?  I'm using a date table where I'm using the field Date

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi Del235 

    TOTALYTD creates YTD value for the year in the current context. It will continue to calculate up to the last date in your dates table.

    If you want to calculate the YTD value up to today's date equivalent in the past years, try this:

    YTD up to today = 
VAR _Today =
    TODAY ()
VAR _EndDate =
    DATE ( MAX ( Dates[Year] ), MONTH ( _Today ), DAY ( _Today ) )
VAR _YrMonthToday =
    FORMAT ( TODAY (), "mmdd" )
VAR _Result =
    CALCULATE (
        SUM ( 'DataTable'[Value] ),
        FILTER (
            ALL ( Dates ),
            Dates[Year] = MAX ( Dates[Year] )
                && Dates[Date] <= MAX ( Dates[Date] )
                && Dates[Date] <= _EndDate
        )
    )
RETURN
    _Result

     

     

4 Replies

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    1 year ago

    Hi Del235 

    Total Through Today =
CALCULATE(
SUM(Sales[SalesAmount]), 
'Date'[Date] <= TODAY() 
)
  • Gabry's avatar
    Gabry
    Icon for Super User rankSuper User
    1 year ago

    Measure = TOTALYTD(sum('yourtable'[yourvalue]), 'datetable'[date])

  • Del235's avatar
    Del235
    Icon for Helper III rankHelper III
    1 year ago

    I thought the TOTALYTD would only total for the current year?

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      1 year ago

      Hi Del235 

      TOTALYTD creates YTD value for the year in the current context. It will continue to calculate up to the last date in your dates table.

      If you want to calculate the YTD value up to today's date equivalent in the past years, try this:

      YTD up to today = 
VAR _Today =
    TODAY ()
VAR _EndDate =
    DATE ( MAX ( Dates[Year] ), MONTH ( _Today ), DAY ( _Today ) )
VAR _YrMonthToday =
    FORMAT ( TODAY (), "mmdd" )
VAR _Result =
    CALCULATE (
        SUM ( 'DataTable'[Value] ),
        FILTER (
            ALL ( Dates ),
            Dates[Year] = MAX ( Dates[Year] )
                && Dates[Date] <= MAX ( Dates[Date] )
                && Dates[Date] <= _EndDate
        )
    )
RETURN
    _Result