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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Robbief
Helper II
Helper II

Running Totals on Selected Dates

Hi 

I've looked, but can't seem to find a solution, and keep running down rabbit holes, so am reaching out for some help / direction on how to tackle this.

 

I have a Transaction Table, with Sales with Dates and Time.  I've got two additional supporting tables for Date and Time respectively.  I've created a measure for Running Total of Transactions throughout the day, and would like to compare any two days of my choosing.

 

My initial idea is to create another Date table, and create two seperate measures, one for Date 1, and the other for Date 2, and have both measures appear on the same graph....  But I appear to be coming unstuck...   

 

This is the measure I am using (Non Date Filtered):

Cumulative Sales (Calendar 1) = IF (
    COUNTROWS ( 'Transaction Line' ) > 0,
    CALCULATE (
        [Sum Transactions],
        FILTER (
            ALLSELECTED ( 'Time Table'[Time] ),
            'Time Table'[Time] <= MAX ( ( 'Time Table'[Time]) )
        )
    ),
    BLANK ()
)
 
IS my approach right?  If not, what approach would you consider?

 

1 ACCEPTED SOLUTION
Robbief
Helper II
Helper II

Thanks for the pointer.  In the end I used USERELATIONSHIP to achieve exactly what I wanted:   

**Except for Dynamically naming the series based on the slicer date selection............  😞  

 

Robbief_0-1664990310286.png

 

View solution in original post

3 REPLIES 3
Robbief
Helper II
Helper II

Thanks for the pointer.  In the end I used USERELATIONSHIP to achieve exactly what I wanted:   

**Except for Dynamically naming the series based on the slicer date selection............  😞  

 

Robbief_0-1664990310286.png

 

Robbief
Helper II
Helper II

I could see that...  But am not sure on how to create a measure on a table that isn't connected in the Schema - Do I create a "virtual join" in the code (Using something like Lookupvalue) ?

Greg_Deckler
Super User
Super User

@Robbief If you are choosing 2 dates that are both connected to the same fact table via something like slicers, then this will cause problems. You would need those tables to be disconnected tables (no relationship) for that to work.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.