CODEWITHSUNDEEP
powerbidax
Ergon
Ergon

Reputation: 1

PowerBi Measure with Variables help requested

I am struggeling with a measure to calculate the average of each 2 values.

The problem is the "VAR MyIndex" (or more likely my lack of DAX skills) . If I create a table with "VAR MyTable" and add a column with "VAR MyIndex" and "VAR __result". I get the expected output in a table.

An approach with rolling average doesnt work, as there are missing dates between the values.

What I created so far is a measure like this:

RollingAvg =

VAR MyTable =          
SUMMARIZE ( Table1, 
             Table1[Datum],
             "index", RANKX(ALLSELECTED(Table1),Table1[Datum],,ASC), 
             "AVG" , [myMeasure]) 

VAR MyIndex =  MyTable,[index]  --- This variable fails due to wrong syntax

VAR __result  =      CALCULATE(AVERAGE([AVG]),
                        FILTER(MyTable, 
                            [index] > MyIndex -2 && 
                            [index] <= MyIndex     ))
RETURN
__result

Any help would be much appreciated. Thanks in advance!

This is a table I can create and add a column with the xpected result.

creating the expected result in a table

So I want this rolling average calculation in a measure to be dynamic.

This is the mesaure i created:

AVG = 

VAR fest       = AVERAGE(Table1[FEST])
VAR avg_all    = CALCULATE(AVERAGE(Table1[FCI]), 
                                    FILTER(ALLSELECTED(Table1), 
                                        Table1[MATERIAL] = MAX (Table1[MATERIAL] &&
                                        Table1[PLANT] = MAX (Tabele1[PLANT] )) ))
                                        
VAR mwtr       = CALCULATE(AVERAGEX(Table1,Table1[FCI] - avg_all), Table1[FCI] <> BLANK()) 

VAR result     = mwtr + avg_all - fest

RETURN
result

FOr this I need the rolling Average - not by date - but row by row.

Upvotes: 0

Views: 315

Answers (1)

Peter
Peter

Reputation: 12365

How about using the build-in Quick measure for Rolling average?
But as with any kind of DAX time-intelligence you need a separate Date table.

If you need more help you have to provide sample data in copyable markdown format.

Upvotes: 0

Related Questions