YTD vs last value

[Anonymous]

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

[Anonymous]

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

[amitchandak]

@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.

[Ashish_Mathur]

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.

[DataInsights · Accepted solution]

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]

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