DAX mesure for row value - coefficients in periods

[dariaglb]

Hi

I have calendar table and  projects's table with milestones

The project has different coefficients in month at different periods of time.

Period 2 may  not happen

Project	Period 1	Period 2	Period 3	Period 4
Number_1	01.01.2023	05.06.2023	01.09.2023	10.11.2023
Number_2	02.03.2023	10.07.2023	01.09.2023	null
Number_3	02.03.2023	23.07.2023	null	null
Number_4	04.05.2023	10.12.2023	null	null
Number_10	01.10.2023	12.12.2023	01.02.2024	null
Number_5	10.10.2023	null	02.02.2024	null

I need mesure for calculation coefficients for chart and cards, which depends on Calendar table 
before Periods - coeff - 4/month,
if Period 1 (+) - coeff 5/month,
if Period 2 (+) - coeff - 7/month  after Period 3 (+) - coeff will be 3/month, after Period 4 (+) - coeff will be 1/month,
if Period 2 (-) after Period 3 (+) - coeff will be 1/month, , after Period 4 (+) coeff will be 0

Thanks in advance 🙂 

[bhanu_gautam · Accepted solution]

@dariaglb , Try below method

Create a Calendar Table: Ensure you have a calendar table in your Power BI model.

Create Relationships: Make sure your project table is related to the calendar table based on the date fields.

Create the Measure: Use the following DAX code to create the measure for calculating the coefficients:

Coefficient Measure =
VAR CurrentDate = MAX('Calendar'[Date])
VAR Period1Date = SELECTEDVALUE('Project'[Period 1])
VAR Period2Date = SELECTEDVALUE('Project'[Period 2])
VAR Period3Date = SELECTEDVALUE('Project'[Period 3])
VAR Period4Date = SELECTEDVALUE('Project'[Period 4])

RETURN
SWITCH(
TRUE(),
CurrentDate < Period1Date, 4,
CurrentDate >= Period1Date && (ISBLANK(Period2Date) || CurrentDate < Period2Date), 5,
CurrentDate >= Period2Date && (ISBLANK(Period3Date) || CurrentDate < Period3Date), 7,
CurrentDate >= Period3Date && (ISBLANK(Period4Date) || CurrentDate < Period4Date),
IF(ISBLANK(Period2Date), 1, 3),
CurrentDate >= Period4Date,
IF(ISBLANK(Period2Date), 0, 1),
0
)

Conditions:
If the current date is before Period 1, the coefficient is 4.
If the current date is after Period 1 but before Period 2 (or Period 2 is blank), the coefficient is 5.
If the current date is after Period 2 but before Period 3 (or Period 3 is blank), the coefficient is 7.
If the current date is after Period 3 but before Period 4 (or Period 4 is blank), the coefficient is 3 if Period 2 is not blank, otherwise 1.
If the current date is after Period 4, the coefficient is 1 if Period 2 is not blank, otherwise 0.

[dariaglb]

Hi, thank you for great solution,

but is it possible to sum coeff of all Projects?
With your mesure chart looks this (is selected one project)

but if - all selected it looks this