Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RHarley
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
v-juanli-msft
Community Support
Community Support

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

 

 

View solution in original post

RHarley
Frequent Visitor

@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!

View solution in original post

11 REPLIES 11
RHarley
Frequent Visitor

@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
Community Support
Community Support

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

 

 

@v-juanli-msft

Hi Maggie,

 

Edit: Can I bug you to do a growth percentage calc as well? For example, Dev Qtr 1 divided by Dev Qtr 0. Then Dev Qtr 2 divided by Dev Qtr 1. Set up as the same matrix, but showing the period on period percentage rather than the running totals. That'll cover absolutely everything I need. Not being cheeky, this was part of my original query 🙂

 

I'll have time to look in a few hours, but it looks very promising! Will mark as solution once I have a quick look.

 

Thanks so much!!

RHarley
Frequent Visitor

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

RHarley
Frequent Visitor

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.

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

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.

 

 

 

 

 

 

AlB
Super User
Super User

Hi @RHarley

 

Could you share the pbix?

RHarley
Frequent Visitor

Hi @AlB,

 

Looks like others have similar issues. 

 

Here is a OneDrive link:

 

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

 

Thanks.

RHarley
Frequent Visitor

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.

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)  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.