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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
majans
Frequent Visitor

Running Total by Date and Category

 Hi All,

 

After several hours studying running total by category/cumultative totals by category/accumulation patterns on this forum and others, I'm unable to find a solution to my problem 🙂

 

My current measure is:

 

majans_0-1656233695979.png

 

 

 

Howver, when I add column "REFERENCE" which is a transaction type, the pattern breaks as below:

 

majans_0-1656233445097.png

1 ACCEPTED SOLUTION

@majans 
Could be but before investigating further please try 

=
SUMX (
    FILTER (
        CROSSJOIN ( ALL ( REFERENCE[REFERENCE] ), ALL ( 'DAILY'[DATE] ) ),
        DAILY[DATE] <= MAX ( DAILY[DATE] )
    ),
    CALCULATE ( SUM ( DAILY[QTY] ) )
)

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

@majans 

please try

VAR CLosingBalance =
CALCULATE (
    SUM ( DAILY[QTY] ),
    REMOVEFILTERS ( 'DAILY' ),
    VALUES ( 'DAILY'[ITEMID] ),
    'DAILY'[DATE] <= MAX ( 'DAILY'[DATE] )
)

Hi @tamerj1 ,

 

Tried your solution however I'm not getting desired result.  See below CBTEST is current measure and CBTEST2 is your suggested solution..  Note that CBTEST gives correct result in first table however is not able to handle the additional Dimension "REFERENCE".  Any ideas?

 

majans_0-1656283679679.png

 

With REFERENCE DIMENSION

 

majans_1-1656283706781.png

 

@majans 

Please try

=
SUMX (
    FILTER (
        CROSSJOIN ( ALL ( Dim[Reference] ), ALL ( 'DAILY'[DATED] ) ),
        Dim[Reference] = MAX ( Dim[Reference] )
            && DAILY[DATE] <= MAX ( DAILY[DATE] )
    ),
    CALCULATE ( SUM ( DAILY[QTY] ) )
)

 

Thank you @tamerj1 I have tried your suggested measure as below:

SUMX (
    FILTER (
        CROSSJOIN ( ALL ( REFERENCE[REFERENCE] ), ALL ( 'DAILY'[DATE] )),
        REFERENCE[REFERENCE] = MAX ( REFERENCE[REFERENCE] ) &&
  

             DAILY[DATE] <= MAX ( DAILY[DATE] )
    ),
    CALCULATE ( SUM ( DAILY[QTY] ) )
)

Resulting in below: 

majans_0-1656323625995.png

 

Should it make a difference that there are other Dimensions in this table like ITEMID, PLANVERSION, WAREHOUSEID etc?

@majans 
Could be but before investigating further please try 

=
SUMX (
    FILTER (
        CROSSJOIN ( ALL ( REFERENCE[REFERENCE] ), ALL ( 'DAILY'[DATE] ) ),
        DAILY[DATE] <= MAX ( DAILY[DATE] )
    ),
    CALCULATE ( SUM ( DAILY[QTY] ) )
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.