Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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
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])
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])
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,
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!!
Seems like I can't get development periods as measures either. Guessing this isn't possible to model in PowerBI then?
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
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.
Hi @AlB,
Looks like others have similar issues.
Here is a OneDrive link:
https://1drv.ms/u/s!AjvCtuEnWiWzgwpT_mPoJi1WsiQt
Thanks.
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.
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |