Measure based on values of three columns with a special formula

[Anonymous]

Hello great PBI Community!

Can you please help me to develop a measure? 

As you see below I have some data on the beginning of each month for three years, where the "Parameter" column needs to be normalised using a "Correction Factor" for each month. 

Here is the formula that I intend to use: 

Corrected Parameter = ((Parameter in any specific month)-min(Parameters in the last 12 month))*Correction factor+min(Parameters in the last 12 month)

Note: for the first year indeed "min(Parameters in the last 12 month)" might be tricky as data is not available for 12 months. 

[v-shex-msft]

HI @ehsanb,

Did these suggestions help with your scenario? If this is a case, you can choose the best one to accept it to help others who faced the same issue to find it more quickly.
If the above not help, please feel free to post here with detailed information and description, then we can test and troubleshoot on it.

Regards,
Xiaoxin Sheng

[Anonymous]

@amitchandak I used your advice to develop a new measure to find part of the measure I intended (see below formula and graph): 

min(Parameters in the last 12 month)

MinParameterLast12Months = CALCULATE(MIN(Blad1[Parameter]),DATESINPERIOD(Blad1[Date], MAX(Blad1[Date]),-12,MONTH))

Can you help me to complete the measure?

Corrected Parameter = ((Parameter in any specific month)-min(Parameters in the last 12 month))*Correction factor+min(Parameters in the last 12 month)

[v-shex-msft]

HI @Anonymous,

You can try to use the following measure formula if this suitable for your requirement:

Measure =
VAR currDate =
    MAX ( Table[Date] )
VAR prevPara =
    CALCULATE (
        MIN ( Table[Parameter] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date]
                = DATE ( YEAR ( currdate ), MONTH ( currDate ) - 12, DAY ( currDate ) )
        )
    )
RETURN
     ( MAX ( Table[Paramter] ) - prevPara ) * 'Correction factor' + prevPara

If the above not help, it will help if you share some dummy data with raw table structures to test:

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , you can use a formula like this with date table

Rolling 12 = CALCULATE(max(Table[Correction Factor]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

For other refer

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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 :radacad sqlbi My Video Series Appreciate your Kudos.