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
cgardyne
Helper I
Helper I

Sum values based on Axis date

I currently have a measure which gives me a running balance of a product based on date as follows:

 

Running Total Computers = CALCULATE (
    SUMX (Loans, Loans[Amount] + Loans[Fee] ),
    FILTER ( 
        ALL ( Loans ),
        Loans[Sixy Days After SignUp] <= MAX(Loans[Sixy Days After SignUp])                     
        && Loans[LoanProductId] = 1
    )
)

In my graph I use Loans[Sixy Days After SignUp] as an Axis and this works well.

 

But what I need to do now is go back 60 days from today which I also have a measure called Loans[Sixy Days From Today] and from this date until today calculate the days balance for a Loan based on product. Not a running balance, just a one off balance for the day and then plot that on the graph.

 

Any ideas? I can share the pbix file if needed.

1 ACCEPTED SOLUTION

Hi @cgardyne,



the last problem I need is on the days where there's no loans for it to record as 0?

Could you try add '+ 0' after SUMX (Loans, Loans[Amount] + Loans[Fee]) to see if it works? Smiley Happy

Running Total Computers =
CALCULATE (
    SUMX ( Loans, Loans[Amount] + Loans[Fee] )
        + 0,
    FILTER (
        ALL ( Loans ),
        Loans[User Sign Up Date] >= MIN ( 'Calendar'[Date] )
            && Loans[User Sign Up Date] <= MAX ( 'Calendar'[Date] )
            && Loans[LoanProductId] = 1
    )
)

 

Regards

View solution in original post

2 REPLIES 2
cgardyne
Helper I
Helper I

Ok I've changed the approach a bit. I've now added a Calendar table which has a row for each day back 60 days. I'm now using that as my Axis on the graph and my measure has changed to:

 

Running Total Computers = CALCULATE (
    SUMX (Loans, Loans[Amount] + Loans[Fee]),
    FILTER ( 
		ALL (Loans),
		Loans[User Sign Up Date] >= Min('Calendar'[Date]) && Loans[User Sign Up Date] <= Max('Calendar'[Date]) && Loans[LoanProductId] = 1
	)
)

the last problem I need is on the days where there's no loans for it to record as 0?

Hi @cgardyne,



the last problem I need is on the days where there's no loans for it to record as 0?

Could you try add '+ 0' after SUMX (Loans, Loans[Amount] + Loans[Fee]) to see if it works? Smiley Happy

Running Total Computers =
CALCULATE (
    SUMX ( Loans, Loans[Amount] + Loans[Fee] )
        + 0,
    FILTER (
        ALL ( Loans ),
        Loans[User Sign Up Date] >= MIN ( 'Calendar'[Date] )
            && Loans[User Sign Up Date] <= MAX ( 'Calendar'[Date] )
            && Loans[LoanProductId] = 1
    )
)

 

Regards

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