Forum Discussion

RHarley's avatar
RHarley
Frequent Visitor
7 years ago
Solved

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

Hi all,   Edit: OneDrive link to .pbix file: https://1drv.ms/u/s!AjvCtuEnWiWzgwpT_mPoJi1WsiQt   Really struggling with this, I've been trawling through the forums for a couple of days. I'd like a...
Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

     

     

  • RHarley's avatar
    RHarley
    7 years ago

    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)
    )
)
     
     
     
    Then used a DIVIDE:
     
    Dev% = DIVIDE([RunTot2],[RunTot2Prior])
     
    Thanks for all of your input!
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi RHarley

As tested, the "DevMonth" and "DevQtr" can be measures

Measure = DATEDIFF(MAX(Data[LodgeMonth]),MAX(Data[EffMonth]),MONTH)

Measure 2 = ROUNDUP([Measure]/3,0)

Then function the same as columns

 

Best Regards

Maggie

RHarley's avatar
RHarley
Frequent Visitor
7 years ago

Hi v-juanli-msft,

 

Thank you for the measures. Now that they are measures though, I can't add them as row labels into a matrix which is what is in the original query I had.

 

Any idea on how to get a running total, with lodge months as columns, and development months or quarters as row labels, with the Amount as values? See the last screenshot in my original post. It has the layout I want, but the quick measure running total doesn't quite give me a running total. It has gaps where no data exists in a particular period.

 

Edit: The tables in the TEST file I uploaded also have the matrix layouts I would like. There are gaps in the running totals. Ideally I'd like a running total with no gaps, and then the same table but displayed as period on period development growth percentages.

 

Thanks again.