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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
PeteSil
Frequent Visitor

Summing Data Until Date Selected

Hello,

 

I am trying to sum up an amount until the date chosen in the slicer. So for example I have the below data:

 

Date               Batch            Hours

1/1/2023         BZ89               10

1/1/2023         BZ90               11

1/1/2023         CX55               10

1/10/2023       BZ89                8

1/10/2023       BZ90               10

1/10/2023       CX55               10

1/25/2023       BZ89               10

1/25/2023       BZ90                8

1/25/2023       CX55               10

 

So now I am trying to have a column in my matrix, represent the total hours to date for a certain BATCH. If week 1/27/23, was chosen it would show the below results in the matrix/

 

BATCH             HOURS

 

BZ89                 28

BZ90                 29

CX55                 30

 

My issue is, that when I select a week from the slicer, it is only representing the hours associated with that week for the batch, instead of showing it all summed.

 

Any help will be greatly appreciated.

 

 

1 ACCEPTED SOLUTION
PeteSil
Frequent Visitor

I was actually able to solve it myself. Was overthinking the DAX formula. Maybe it will help someone else, so I posted it below.

 

BATCH Hours to Date =
VAR SelDate = SELECTEDVALUE('Ultimate Calendar'[WeekDay])
RETURN
CALCULATE(sum('ADP Batch Hours'[Hours]), 'Ultimate Calendar'[WeekDayCorrect] <= SelDate)

View solution in original post

3 REPLIES 3
PeteSil
Frequent Visitor

I was actually able to solve it myself. Was overthinking the DAX formula. Maybe it will help someone else, so I posted it below.

 

BATCH Hours to Date =
VAR SelDate = SELECTEDVALUE('Ultimate Calendar'[WeekDay])
RETURN
CALCULATE(sum('ADP Batch Hours'[Hours]), 'Ultimate Calendar'[WeekDayCorrect] <= SelDate)
Ritaf1983
Super User
Super User

Hi @PeteSil 
If I understand you correctly this tutorial should help.

https://www.youtube.com/watch?v=YWfLrezEc84

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Unfortunately didn't work :(. 

Following that video just summed up all the hours, but the hours weren't changing by week selected. So if I went back a month earlier, I would expect to see less hours, but it was keeping them the same total.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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