cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Microsoft

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?

```Running Total Computers =
CALCULATE (
SUMX ( Loans, Loans[Amount] + Loans[Fee] )
+ 0,
FILTER (
ALL ( Loans ),
&& Loans[LoanProductId] = 1
)
)
```

Regards

2 REPLIES 2
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),
)
)```

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

Microsoft

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?

```Running Total Computers =
CALCULATE (
SUMX ( Loans, Loans[Amount] + Loans[Fee] )
+ 0,
FILTER (
ALL ( Loans ),
&& Loans[LoanProductId] = 1
)
)
```

Regards

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors