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:

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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.