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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ArbWare
New Member

Group Running total for date and type

Working on this, and i'm stuumped

I can get to group riunning toital by date, but not by Type/Date combined

Any help would be sorely appreciated

 

TypeDateValue
a1/1/20221
a2/1/20222
a3/1/20223
a4/1/20224
a5/1/20225
a6/1/20226
a7/1/20227
a8/1/20228
a9/1/20229
a10/1/202210
a11/1/202211
a12/1/202212
   
a1/1/20231
a2/1/20232
a3/1/20233
a4/1/20234
a5/1/20235
a6/1/20236
a7/1/20237
a8/1/20238
a9/1/20239
a10/1/202310
a11/1/202311
a12/1/202312
   
b1/1/202213
b2/1/202214
b3/1/202215
b4/1/202216
b5/1/202217
b6/1/202218
b7/1/202219
b8/1/202220
b9/1/202221
b10/1/202222
b11/1/202223
b12/1/202224
   
b1/1/20231
b2/1/20232
b3/1/20233
b4/1/20234
b5/1/20235
b6/1/20236
b7/1/20237
b8/1/20238
b9/1/20239
b10/1/202310
b11/1/202311
b12/1/202312

 

Measure

ToDate=CALCULATE(

SUM(Table1[Value]),

filter( all(Table1), isonorafter(Table1[Date],max(Table1[Date]),DESC) ))

 

Actual Output    
TypeDate (Year)Date (Quarter) Sum of ValueToDate
a2022Qtr1 648
a2022Qtr2 15114
a2022Qtr3 24198
a2022Qtr4 33300
a2022 Total  300
a2023Qtr1 6312
a2023Qtr2 15342
a2023Qtr3 24390
a2023Qtr4 33456
a2023 Total  456
a Total   156456
      
b2022Qtr1 4248
b2022Qtr2 51114
b2022Qtr3 60198
b2022Qtr4 69300
b2022 Total  300
b2023Qtr1 6312
b2023Qtr2 15342
b2023Qtr3 24390
b2023Qtr4 33456
b2023 Total  456
b Total   300456
      
Grand Total   456

 

Desired Output

TypeDate (Year)Date (Quarter)Sum of ValueToDate
a2022Qtr166
a2022Qtr21521
a2022Qtr32445
a2022Qtr43378
a2022 Total 7878
a2023Qtr1684
a2023Qtr21599
a2023Qtr324123
a2023Qtr433156
a2023 Total 78156
a Total  156156
     
b2022Qtr14242
b2022Qtr25193
b2022Qtr360153
b2022Qtr469222
b2022 Total 222222
b2023Qtr16228
b2023Qtr215243
b2023Qtr324267
b2023Qtr433300
b2023 Total 78300
b Total  300456
     
Grand Total  456456
1 ACCEPTED SOLUTION
ArbWare
New Member

Thank you all for the guidance, 

I found the desired result through the use of "AllExcept"

 

ToDate=CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Type] ),
Table1[Date] <= MAX ( Table1[Date] )
)
)

 

Just preliminary, but i think it does what i'm looking for... 

 

Love the forum.. thanks for giving the guidance that led me to a solution that works.

 

View solution in original post

4 REPLIES 4
ArbWare
New Member

Thank you all for the guidance, 

I found the desired result through the use of "AllExcept"

 

ToDate=CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Type] ),
Table1[Date] <= MAX ( Table1[Date] )
)
)

 

Just preliminary, but i think it does what i'm looking for... 

 

Love the forum.. thanks for giving the guidance that led me to a solution that works.

 

parry2k
Super User
Super User

 @newtodaxI wouldn't go that path, there are 2 ways to solve a problem, improve the model and write a simple DAX, or write a complex DAX and then deal with performance and other challenges. Just my 2 cents.  



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

newtodax
Frequent Visitor

Try using the following measure and see if that works. 

 

Running Total by Category Group =
VAR CategoryGroupFilter = SELECTEDVALUE(Table1[Type])
VAR RunningTotal =
    IF(
        CategoryGroupFilter = "a",
        SUMX(
            FILTER(
                ALL(Table1),
                Table1[Type] = "a" &&
                Table1[Date] <= MAX(Table1[Date])
            ),
            Table1[Value]
        ),
        IF(
            CategoryGroupFilter = "b",
            SUMX(
                FILTER(
                ALL(Table1),
                Table1[Type] = "b" &&
                Table1[Date] <= MAX(Table1[Date])
            ),
            Table1[Value]
        ),
            BLANK()
        )
    )
RETURN
    RunningTotal
parry2k
Super User
Super User

@ArbWare 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

 

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

 

Once you have a date dimension, use that in your measure to get the running total and also in the visualization.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors