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

     

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
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
        )
    )

 

 

  • SingSong's avatar
    SingSong
    Frequent Visitor
    7 years ago

    Many thanks for your solution, worked perfectly

     

    SingSong