Running Total: Non-date. Blank issues, % movement

Hi RHarley

I make a test for " running total for date-quarter"

1. create a new table by entering this code

new Table =
ADDCOLUMNS (
 CROSSJOIN ( VALUES ( Data[DevQtr] ), VALUES ( LodgeDateTable[QuartEnd] ) ),
 "Date_Qtr", FORMAT ( [QuartEnd], "mmm-yy" )
)

Then add calcualted columns in this table

IndexCol = CONCATENATE(CONCATENATE([DevQtr],"-"),[Date_Qtr])

2. add a calculated column in "Data" table

IndexCol = [DevQtr]&"-"&RELATED(LodgeDateTable[Date_Qtr])

then create a relationship between "Data" and "new Table" based on "IndexCol" column

3.create a measure in  "new Table"

RunTot2 = 
CALCULATE(
	[TotalAmount],
	FILTER(
		ALLSELECTED('new Table'[DevQtr]),
		ISONORAFTER('new Table'[DevQtr], MAX('new Table'[DevQtr]), DESC)
	)
)

add "Date_Qtr" , "DevQtr" and "RunTot2"  from "new Table" in the visual, 

sort column header "Date_Qtr" by "QuartEnd" in  "new Table" :

click in the column "Date_Qtr", click "sort by column" and then select "QuartEnd".

Best Regards

Maggie

v-juanli-msft

Hi Maggie,

I've managed to figure out a way on my own for the growth percentage part.

I copied your running total formula, and modified with a -1:

RunTot2Prior =
CALCULATE(
    [TotalAmount],
    FILTER(
        ALLSELECTED('new Table'[DevQtr]),
        ISONORAFTER('new Table'[DevQtr], MAX('new Table'[DevQtr])-1, DESC)
    )
)

Dev% = DIVIDE([RunTot2],[RunTot2Prior])