cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RHarley Frequent Visitor
Frequent Visitor

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 running total by "Development Quarter", which is not a date based number itself. It is simply a count of how many quarters have passed since the original lodgement date of a document. I can't run "Development Quarter" as a date, as I won't be able to compare Development Quarters across different lodgement months or periods. 

 

For information, period 0 is the lodgement month. Periods after this start from 1, either as development months or development quarters.

 

Ideal outcomes:

 

1. Running total whereby if there is no data, it is just the total from the previous development period

2. These running totals as period on period percentage changes. E.g. August 2016 Dev Quarter 1 would be 60,300/2,900 = 2,079.31% - the gaps in the running total really ruin this, particularly if trying to create graphs.

If any more information is required, please let me know.

 

Thanks!

 

image.pngimage.png

image.png 

 

 

image.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: 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, 

 

4.png

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 Frequent Visitor
Frequent Visitor

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

@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!
11 REPLIES 11
Super User
Super User

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

Hi @RHarley

 

Could you share the pbix?

RHarley Frequent Visitor
Frequent Visitor

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

Hi @AlB,

 

Thanks for replying. I can't seem to find an attachment button anywhere? Only photos or videos. 

 

I found a forum post which shows an attach button below this box, but I certainly don't have that.

Super User
Super User

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

Hi there @RHarley

 

You have to share the URL to the file, either from platforms like Dropbox, OneDrive, etc.   or you can upload the file to sites like this (no sign-in required)  

RHarley Frequent Visitor
Frequent Visitor

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

Hi @AlB,

 

Looks like others have similar issues. 

 

Here is a OneDrive link:

 

https://1drv.ms/u/s!AjvCtuEnWiWzgwpT_mPoJi1WsiQt

 

Thanks.

RHarley Frequent Visitor
Frequent Visitor

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

Starting to think it is something to do with how I've set up the development months and quarters as calculated columns rather than measures. If I could set them up as a measure, I could potentially rank them and do the cumulative total that way? I don't know, still looking through the forums. Can't seem to rank off a calculated column.

Highlighted
RHarley Frequent Visitor
Frequent Visitor

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

Seems like I can't get development periods as measures either. Guessing this isn't possible to model in PowerBI then?

Community Support Team
Community Support Team

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

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

2.png

 

Best Regards

Maggie

RHarley Frequent Visitor
Frequent Visitor

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

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.

 

 

 

 

 

 

Community Support Team
Community Support Team

Re: 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, 

 

4.png

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