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

 

 

 

 

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!

11 Replies

    • RHarley's avatar
      RHarley
      Frequent Visitor
      7 years ago

      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.

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        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's avatar
    RHarley
    Frequent Visitor
    7 years ago

    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.

    • v-juanli-msft's avatar
      v-juanli-msft
      Community 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.

         

         

         

         

         

         

  • RHarley's avatar
    RHarley
    Frequent Visitor
    7 years ago

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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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
      Frequent Visitor
      7 years ago

      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's avatar
    RHarley
    Frequent Visitor
    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!