Forum Discussion

cgardyne's avatar
cgardyne
Helper I
9 years ago
Solved

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 ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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? :smileyhappy:

    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

cgardyne's avatar
cgardyne
Helper I
9 years ago

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?

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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? :smileyhappy:

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