Calculated Column & Référence to one line.

[MathLacome]

Hi everyone, 

I'm a fresh user of Power BI (less than 6 months experience) but OK in Excel.

I have some dashboard that I would like to move from Excel to Power BI but still struggling with some calculated rows.

In Excel, I have a moving mean  (or sum) calculated for each different people with basics formula like : 

SUMIFS((Total Distance]; [Date]; "<="[@Date];[Date];">"&[@Date]-7;[Player];[@Player])

I switched in Power Bi with a simple : 

Calculate(sum([Total Distance)]; Datesinperiod (...) ; 

But I don't know what to add to replace my player filter [@Player]...

Anyone could help me ?

All the best,

Mathieu.

[v-shex-msft]

Hi @MathLacome,

Since I'm not very clear with your variables [@Date] and [@Player], can you share me some detail information about these?

I try to convert your formula to dax, perhaps you can try to use it.

if the @ variables means current value:

Total=
var currDate=MAX([Date])
var currPlayer=LASTNONBLANK(Table[Player],[Player])
return
SUMX(FILTER(ALL(Table),AND([Date]<=currDate,[Date]>currDate-7)&&[Player]=currPlayer),[Price])

if the @ variables means selected value:

Measures:

SelectDate=if(HASONEVALUE(Table[Date]),VALUES(Table[Date]),BLANK())

SelectPlayer=if(HASONEVALUE(Table[Player]),VALUES(Table[Player]),BLANK())

Total=
SUMX(FILTER(ALL(Table),AND([Date]<=[SelectDate],[Date]>[SelectDate]-7)&&[Player]=[SelectPlayer]),[Price])

If above is not help, please share us more detail information.

Regards,

Xiaoxin Sheng

[BhaveshPatel · Accepted solution]

First Requirement:

You should create a calender table in your data model and create a relationship between your date table and facttable(Transactions Table).

To Create Total 12 months moving average Distance Measure, You should create the below measures.

TotalDistance:=SUM(FactTable[Total Distance])

TotalDistance12Mon := CALCULATE (

    [TotalDistance],

    DATESBETWEEN (

        Calendar[Date],

        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[Date] ) ) ),

        LASTDATE ( Calendar[Date] )

    )

)

Create a third measure to count the number of periods in a transactions table

Months12 := CALCULATE (

    CALCULATE ( COUNTROWS ( VALUES ( Calendar[MonthName] ) ), FactTable),

    DATESBETWEEN (

        Calendar[Date],

        NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( Calendar[Date] ) ) ),

        LASTDATE ( Calendar[Date] )

    )

)

For moving average of 12 months:

Create a fourth measure as shown below.

MovingAverage12months:= DIVIDE ( [TotalDistance], [TotalDistance] ) * DIVIDE ( [TotalDistance12Mon], [Months12] )

[Seth_C_Bauer]

@MathLacome I'm not an Excel guy, but if I read the function correctly you are basically adjusting the formula for each person... If that's the case, then you can just create the measure without that part. In Power BI, you would use a slicer or filter on the report page to adjust the calculation. Otherwise, if the measure is used along with the person in the visual, the measure will automatically aggregate with any related field.