Forum Discussion
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
Mar | 130 | Cost
Mar | 8 | HC
There is a filter Type with "HC" vs "Cost"
Using the time intelligence feature from Power BI, I get 340€ from Jan to Mar. Fine.
But if I select "HC" in the Type filter, I get of course 19 HC (the sum of all HC) when I expect to get the last value 8.
How can I achieve that?
Thanks for your help
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:
5 Replies
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.
Anonymous , Try a new measure like with time intelligence in case you have date
CALCULATE(SUM(costandhc[Amount]), DATESYTD('Date'[Date]),costandhc[Type] = "Cost") +CALCULATE(lastnonblankvalue('Date'[Date],SUM(costandhc[Amount])),costandhc[Type] = "HC")To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.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
