cancel
Showing results for
Did you mean:

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

Frequent Visitor

## Cumulative values from Specific Date

Hello ,

Let's say I have a Forecast table with the following data

 Date Sales 05/2022 10 06/2022 20 07/2022 30 08/2022 40 09/2022 50 10/2022 60

I need to create a table graph that will always start cumulating the values from M-1 (based on today's date) and put whatever is  before it as 0 like below  :

 Date Cumulated Values 05/2022 0 06/2022 0 07/2022 0 08/2022 40 09/2022 90 10/2022 150

How can I achieve this ?

Best Regards,

1 ACCEPTED SOLUTION
Community Support

Here I create a sample to have a test and I think you can try below code to create a measure.

Sample:

Measure:

``````Measure =
VAR _START = EOMONTH(TODAY(),-2)+1
RETURN
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Date]>=_START&&'Table'[Date]<=MAX('Table'[Date])))+0``````

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support

Here I create a sample to have a test and I think you can try below code to create a measure.

Sample:

Measure:

``````Measure =
VAR _START = EOMONTH(TODAY(),-2)+1
RETURN
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Date]>=_START&&'Table'[Date]<=MAX('Table'[Date])))+0``````

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thank you @v-rzhou-msft , it worked perfectly. I really appreciate it

New Member

I see two issues there.

First one to solve is the date filter. I used a variable, where I filtered the Sales by TodayM-1.
Second issue is much easier as you can cumulated the variable as any other cumulated sum by filtering allselected dates with isonorafter(Date, max(date), descendants).

I hope this one helps you:

``````Cumulated Values =

var SalesfromMminus1 = Calculate(Sum(Sales[Sales]),Filter('Dim Datum','Dim Datum'[DatumBK] >= DATE (YEAR (TODAY()), MONTH ( TODAY () ) - 1, DAY ( TODAY () - 1 ) )))
return
CALCULATE(
SalesfromMminus1,
FILTER(
ALLSELECTED('Dim Datum'[DatumBK]),
ISONORAFTER('Dim Datum'[DatumBK], MAX('Dim Datum'[DatumBK]), DESC)
)
)``````

____________________________

You want more helpful tipps and tricks like that?

Frequent Visitor

Hello @TypimWuerfel

Thank you for your reply. However, the measure doesn't work and it returns the same value as the non cumulated. Please note that my measure that I want to comulate is as follows (I don't know if this changes anything) :

CALCULATE([Value],PARALLELPERIOD('D_Calendar'[Date],1,MONTH))

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