cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

How to I populate data based on dynamic contract term (in Months)

Hi everyone,

I am stuck with one particular dax measure and I need help on this. I am trying to populate data based on the contract term (which is in month). So basically, the data set that I have is:

1. I have multiple projects with different contract terms
2. Contract term is calculated from two different column (i.e start and end date)
3. I want to calculate total financial value based on contract term (i.e if the contract terms is 60 months then financial value should have 60 months of data from the start date of the project. If the contract term is 84 months then financial value should have 84 months of data from the start date of the project.
4. Financial value and contract term are coming from two different tables

regards,

Nikhil

4 REPLIES 4
Super User
``````TotalFinancialValue = //Try this one and replace tableName and columnName with your actuals
VAR CurrentProject = SELECTEDVALUE(Projects[ProjectID])
VAR StartDate = CALCULATE(MIN(Projects[StartDate]), Projects[ProjectID] = CurrentProject)
VAR ContractTerm = SELECTEDVALUE(Projects[ContractTerm])

RETURN
CALCULATE(
SUMX(
FILTER(
FinancialData,
FinancialData[ProjectID] = CurrentProject &&
FinancialData[Date] >= StartDate &&
FinancialData[Date] < StartDate + ContractTerm
),
FinancialData[FinancialValue]
)
)``````

Proud to be a Super User!

Frequent Visitor

Thank you @mh2587 , I tried this but without much success. Let me try to explain the scenario is little more elaborately.

1. The contract Term date is coming from a separate table (say - DIM Opp Table) which has to opportunity ID and contract start date and contract end date (Contract term is calculated basis the contract start & end date)

2. I want to calculate a financial metric (X) based on contract term and other metrics (i.e if the contract terms is 60 months then financial value should have 60 months of data from the start date of the project. If the contract term is 84 months then financial value should have 84 months of data from the start date of the project.

3. X is also further calculated basis two other metric (Y & Z)

4. Y I am able to calculate - straight foward metric (lets say I have got 60000)

5. Z is calculated basis Y and contract term- i.e. Y/contract term - so if Y is 60000 and contract term is 60 months then Z is 1000 (6000/60)

6. Now X = Y - Z and this need to be populated 60 times as the contract terms is 60 months

NOTE: contract term is different for each Opportunity

Super User

Did you have a relationship between project & DIM Opp Table ?

Proud to be a Super User!

Frequent Visitor

Yes I do have relationship created between the tables!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.