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 ( 
        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.

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

2 Replies

  • 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