Home Forums Power Pivot cumulative running total blank if no records in a month

This topic contains 8 replies, has 4 voices, and was last updated by  eni13 7 months ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
    Posts
  • #9236

    Jonathan Smith
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    I am trying to create a cumulative running total by month for a balance sheet. I am using this DAX Formula for this and it seems to be working. The  ‘Invoked Function'[Date] is my date table and the  ‘TB D365′[Amount] is my fact table with all transaction for the year.

    =CALCULATE (

    SUM ( ‘TB D365′[Amount]),

    FILTER (

    ALL (  ‘Invoked Function'[Date] ),

    ‘Invoked Function'[Date] <= MAX ( ‘Invoked Function'[Date] )

    )

    )

    However, I am running into an issue. If there is no data in a month, the running total is blank rather than repeating the cumulative running total from the prior month. If there are transactions that sum to zero in a month, then it will repeating the cumulative running total from the prior month. How do I force the cumulative running total from the prior month to show even if there are no transactions in a month? Attached is a screen shot of what I am seeing. Thanks for any help you can give me.

    Attachments:
    You must be logged in to view attached files.
    #9243

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    What version of Excel are you using?

    #9244

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    I am not seeing the behavior you describe: using Excel 2016, I am getting the behavior you are asking for. Please see attached worksheet.

    Usually when running cumulative totals, users are asking how to stop the last value from repeating.

    Attachments:
    You must be logged in to view attached files.
    #9267

    Jonathan Smith
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    Thank you for the response. I am using Microsoft Excel 2016 MSO (16.0.9001.2102) 64-Bit. You are correct. I created a function with just the cumulative balance and it worked so it must be part of my larger function. What i am trying to do is: if transactions are part of the balance sheet use a cumulative running total if not use just the month total. I think i am trying to do to much with one function and need to restructure my data with a calculated column to indicate if an account is BS, P&L, or equity. Then i can get rid of the contains. I will post it if that works. Thank you very much for the help. I attached a copy of the excel if anyone is curious.

    =if(CONTAINS(‘TB D365’,’TB D365′[Account Type],”Asset”)||CONTAINS(‘TB D365’,’TB D365′[Account Type],”Liabilities”)||CONTAINS(‘TB D365’,’TB D365′[Account Type],”Equity”),
    CALCULATE (
    SUM ( ‘TB D365′[Amount]),
    FILTER (
    ALL ( ‘Invoked Function'[Date] ),
    ‘Invoked Function'[Date] <= MAX ( ‘Invoked Function'[Date] )
    )
    ),
    SUM ( ‘TB D365′[Amount]))

    Attachments:
    You must be logged in to view attached files.
    #9270

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    For what you are trying to do, the issue is twofold: model design and formula.

    Focusing on your measure for BSRUNTTLPLSUM:

    Since CONTAINS in the current model will return false if there are no rows to evaluate, the measure will, regardless of whether the row is for Assets, Liabilities or Equity, will *always* follow with the SUM (‘TB D365′[Amount] ) which returns blank when there are no rows to evaluate.

    The simplest fix I can think of for this case is to create a lookup table for account types, and then simplify your formulas.

    As shown in the attached workbook, your new measure for BSRUNTTLPLSUM can look like this:

    BSRUNTTLPLSUM:=IF (
        COUNTROWS ( FILTER ( AccountTypes, [Account Type] = "Asset" || [Account Type] = "Liabilities" || [Account Type] = "Equity" ) ) > 0,
        [Amount To Date], 
        [Total Amount] 
    )

    when you write these measures first:

    Total Amount:=SUM ( 'TB D365'[Amount] )

    Amount To Date:=CALCULATE (
        SUM ( 'TB D365'[Amount] ),
        FILTER (
            ALL ( 'Invoked Function'[Date] ),
            'Invoked Function'[Date] <= MAX ( 'Invoked Function'[Date] )
        )
    )

    Tom

    Attachments:
    You must be logged in to view attached files.
    #9272

    Jonathan Smith
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    WOW! I actually understand this. Not just your correct version but what I did wrong. Thank you so much for taking the time to explain all this. I am new to this but have the data bug now.

    #10476

    deluca97
    Participant
    • Started: 0
    • Replies: 1
    • Total: 1

    hello I have similar problem could you please try to help me?

    Everything is explained in the attached file, including the formula I’m using.

    Cumulative Distinct Properties:=CALCULATE([DistinctProperties],DATESYTD(‘Data'[CreatedOn],”31/03/2019″),ALL(Calendar))

    I’m unable to get rid of blank cells when trying to display running total over a year.

    I would like to see the running total per month and no gaps eg. In July for Word of Mouth I would like to see 10. How can I achieve that?

     

    I also seem to be unable to recreate some tables I managed previously, by using calendar table to plot data and use Month Text, I’ve checked the data type and everything is identical to the separate file I created previously using Excel 2010 but it doesn’t want to work (populates with the up to day total across the whole table instead of running total)

    Would be thankful if you could help me.

    Thank you

    Attachments:
    You must be logged in to view attached files.
    #10481

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    Regarding formula,

    DATESYTD requires the date column from the calendar table (in this case, ‘Calendar'[Full Date] ).

    Also regarding DATESYTD, since you are on a fiscal year ending on March 31, you should not use the default calendar month order. In the calendar table, I created a column [FY Month Sort] with this formula:

    = IF ( 'Calendar'[Month] <= 3, 'Calendar'[Month] + 9, 'Calendar'[Month] - 3 )

    then used that for the Sort By column of [Full Date (Month)].

    Additionally, ALL ( Calendar ) should not be necessary because the time intelligence functions handle the calendar table in their own special way.

    Also, the relationship between the Calendar and Data tables was reversed. Regardless of what is said, I always drag the date column from the fact table (the many-side of the relationship) to the lookup table (the one-side of the relationship) because when the data model observes a one-to-one relationship (no date has multiple properties associated with it), the data model assumes you are dragging from the many-side to the one-side.

    Finally, for your consideration, there is a worksheet with an alternate pivot design.

    Attachments:
    You must be logged in to view attached files.
    #12042

    eni13
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    I have created a year to date formula in Power Pivot to get the Sales cumulated month by month. When I create a pivot table it happens what it is shown below in the picture attached. When on the month to date I have no turnover the year to date formula doesn´t give me a value (but it should give me the same value of the month before).

    On the power pivot this is my formula:

    YTD TGS:=TOTALYTD(SUM(DF_GRID_1[_TGS]);DF_GRID_1[Date Dummey];ALL(‘Date Table1’)).

    Can somebody help me? also with other suggestions? 🙁

    Attachments:
    You must be logged in to view attached files.
Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.