Forum Discussion

tbucki1's avatar
tbucki1
Icon for Advocate I rankAdvocate I
5 years ago

Headcount FTE report needed

I am building a headcount report from a dataset that shows the actual headcount/FTE for a month. I.e  Jan 2020 = 5, Feb 2020 = 6, Mar 2020 = 10...... Jan 2021 = 8....etc... My problem is when I put t...
Need Help
tbucki1's avatar
tbucki1
Icon for Advocate I rankAdvocate I
5 years ago

Hi TomMartens , thanks for the reply. I did review the link you sent and can see from the examples on that page that the measure I want to build is like the "LastDateByCustomerEver" as referenced about halfway down. However my data is in a fact table stacked tall and unpivoted with three columns; Date, Account, and Values. Hence why I have a measure for FTE currently that is "CALCULATE( SUM([Values]), [Account] = "Headcount") How do I take something I already built (FTE) and plug it into this code below? The below code example (from page link you sent) uses balances as a table to do grouping within the summarize function. Not sure how to convert what I have into this....?? and get the new measure I need which i'll call "LastHeadCountValue".... I do have a dates table that is joined to the Date column on my fact table. Star Schema Diagram is the setup here... Thoughts?

Balance LastDateByCustomerEver :=
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxBalanceDates =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Balances, Customers[Name] ),
            "@MaxBalanceDate", CALCULATE ( MAX ( Balances[Date] ) )
        ),
        'Date'[Date] <= MaxDate
    )
VAR MaxBalanceDatesWithLineage =
    TREATAS ( MaxBalanceDates, Customers[Name], 'Date'[Date] )
VAR Result =
    CALCULATE ( SUM ( Balances[Balance] ), MaxBalanceDatesWithLineage )
RETURN
    Result