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 returned

 

   Table 1 / Costs
-----------------------------
TimePeriod   | Date mm/dd/yyyy  | Value
-----------------------------
 A18         | 01/01/2018       | 12345
 A18         | 02/01/2018       | 12345
 ...
 A18         | 12/01/2018       | 12345
 A19         | 01/01/2019       | 12345 
 A19         | 02/01/2019       | 12345 
 A19         | 03/01/2019       | 12345
 A19         | 04/01/2019       | 12345 
 P19         | 01/01/2019       | 12345 
 P19         | 02/01/2019       | 12345
 ... 
 P19         | 12/01/2019       | 12345

I have a date table(Dates) which I have a relationship to the Date column above

 

P19 YTD = TOTALYTD(SUM(Costs[Value]),Dates[Date],FILTER(Costs,Costs[TimePeriod]="P19"))
 
I expected this measure to limit the sum for Jan to May's Plan figure then I was goign to figure out how to go back to last month but it SUM's P19 for Jan to Dec.
 
Any help much Appreciated
 
SingSong
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
        )
    )

     

     

3 Replies

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

     

     

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