Forum Discussion

SingSong's avatar
SingSong
Frequent Visitor
7 years ago
Solved

TotalYTD to Last Month

I have a costs statement source in the below format, I would like to return the Plan 2019 cost figure only to last month... so this month is currently May, I only want the sum from Jan to Apr returne...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi SingSong ,

     

    To create a measure as below.

    Measure = 
VAR pre =
    CALCULATE (
        MAX ( 'CALENDAR'[Date] ),
        FILTER ( 'CALENDAR', DATEDIFF ( 'CALENDAR'[Date], TODAY (), MONTH ) = 1 )
    )
RETURN
    CALCULATE (
        SUM ( Table1[value] ),
        FILTER (
            Table1,
            Table1[TimePeriod] = "P19"
                && YEAR ( 'Table1'[date] ) = YEAR ( pre )
                && 'Table1'[date] <= pre
        )
    )

     

     

littlemojopuppy's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
6 years ago

Hi!  I came across this looking for a solution for a similar problem.  I wanted to offer up the solution I came up with as an alternative or for anyone who might be looking in the future...

    CALCULATE(
        TOTALYTD (
            [Forecasted Contract Count],
            'Calendar'[Date]
        ),
        INTERSECT(
            DATESYTD('Calendar'[Date]),
            PREVIOUSMONTH('Calendar'[Date])
        )
    )