Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

YTD vs last value

I have a table with the monthly cost and headcount at the end of the month   The table is set up like that: Month | Amount | Type Jan | 100 | Cost Jan | 5 |  HC Feb | 110  | Cost Feb  | 6 | HC...
Need Help
  • DataInsights's avatar
    DataInsights
    6 years ago

    Create three measures as shown below. The table is named CostAndHC, and is joined to the Date table. I converted the Month field to a date field (e.g., Jan becomes 1/1/20) in order to join to the date table. This approach assumes a date slicer is used.

     

    EOM Cost =
    CALCULATE (
    TOTALYTD ( SUM ( CostAndHC[Amount] ), 'Date'[Date] ),
    CostAndHC[Type] = "Cost"
    )

     

    EOM HC =
    VAR MaxDate =
    MAX ( 'Date'[Date] )
    VAR MonthMaxDate =
    MONTH ( MaxDate )
    VAR YearMaxDate =
    YEAR ( MaxDate )
    VAR DateToFilter =
    DATE ( YearMaxDate, MonthMaxDate, 1 )
    RETURN
    CALCULATE (
    SUM ( CostAndHC[Amount] ),
    CostAndHC[Type] = "HC",
    CostAndHC[Date] = DateToFilter
    )

     

    EOM Amount =
    VAR SelType =
    SELECTEDVALUE ( CostAndHC[Type] )
    RETURN
    SWITCH ( SelType,
    "Cost", [EOM Cost],
    "HC", [EOM HC]
    )

     

    Then, create a matrix as shown below:

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ，

You can create a calculated column to get month number first, then create a measure to get the amount:

Month Number = SWITCH('Table'[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
Measure = 
VAR _maxMonth =
    CALCULATE (
        MAX ( 'Table'[Month Number] ),
        ALLEXCEPT ( 'Table', 'Table'[Type] )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Type] ) = "HC",
   CALCULATE (
            MAX ( 'Table'[Amount] ),
            FILTER ( 'Table', 'Table'[Month Number] = _maxMonth )
        ),
        CALCULATE ( SUM ( 'Table'[Amount] ) )
    )

Best Regards

Rena