Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Running Total not working

Hi all!   I'm new to Power BI and I'm trying to make a running total of the column 'Projection Base' based on a Date column.  In this example, Projection Base is a measure as follows: 3. Projec...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous

If table "Sheet1" and date table "calendar" have a relationship with a cardinality of "many to one".

please create this measure:

Measure = IF(ISBLANK(MAX(Sheet1[real])),MAX(Sheet1[budget]),MAX(Sheet1[real]))
running total 1 =
SUMX (
 FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[date] <= MAX ( Sheet1[date] ) ),
 [Measure]
)


or 
running total 2 =
SUMX (
 FILTER (
 ALLSELECTED ( 'Sheet1' ),
 ISONORAFTER ( Sheet1[date], MAX ( Sheet1[date] ), DESC )
 ),
 [Measure]
)

 

Best Regards

Maggie

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Maggie,

 

Thanks for the answer. The problem I'm having is that columns 'budget' and 'real' are in different queries, and then the ending balance for each month, both for budget and for real values are measures. Can I work with measures?

 

Thanks in advance,

Douglas

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    'budget' and 'real' are measures and they exsit in different tables, right?

    These two tables can be connected with "date" column, right?

    If so, you could replace the following formula 

    Measure = IF(ISBLANK(MAX(Sheet1[real])),MAX(Sheet1[budget]),MAX(Sheet1[real]))

     with this

    Measure = IF(ISBLANK([real]),[budget],[real])

    Best Regards

    Maggie