Forum Discussion

apatil's avatar
apatil
Frequent Visitor
9 years ago

Adding calculated columns based on lookup table

Hi All,   I am trying to work on a consolidated report which shows beginning headcount, Newhires and other details for each period and year. I have lookup values for year, period, start and end da...
Need Help
TheOckieMofo's avatar
TheOckieMofo
Icon for Resolver II rankResolver II
9 years ago

First off, there are several people that are on the Mt. Rushmore of DAX. One (technically 2) of these people is The Italians. They have a website called DAX patterns that addresses a lot of common issues such as this one. I think you can use this technique to help you:

Cumulative Total

 

You would have to manipulate it a bit, though. But essentially, you should be able to create two joins between the period table and fact table and create a measure that essentially looks like this:

 

Cumulative Quantity :=
CALCULATE (
    Count( Assignment_ID ),
    FILTER (
        ALL ( 'Period'[PeriodStartDate] ),
        'Period'[PeriodStartDate] <= MAX ( 'Period'[PeriodStartDate] )
    )
)
 
I think that should get you the beginning headcount. Hopefully that will get you started.