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:

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
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

Thanks! It savess me hours!

 

Actually it was even simplier

Amount YTD Mix =
VAR SelType =
SELECTEDVALUE ('CoA'[Type] )
RETURN
SWITCH ( SelType,
"Cost (€)", [Amount YTD 2],
"Headcount", HR[Amount M],
"FTE", HR[Amount M]
)

 

with [Amount YTD 2] being the time intelligence Power BI generated field for YTD