Forum Discussion
YTD vs last value
- 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:
Hi,
It is ideal to build a Calendar Table from the Month column, connect the 2 Tables and then use Date intelligence functions to fetch the HC as on the last date.