Cumulative result per month

[jonbox]

Hi, i'm trying to create a measure that accumulates the result per month so that when shown in the line graph, taking the total as an example to be 2.0m in may, 11m june, 11.8m in july, 12.3 in august etc as apposed to how it looks currently.

Current line graph:

Data i'm using:

Actualprediction_USD	Date
2.0m	April
9.0m	May
0.8m	June
0.5m	July
0.8m	August
0.8m	September
0.7	October

My current measure:

total = 
CALCULATE( 
    SUM( eforecastingdata[ActualPrediction_USD]),
    FILTER(
        ALL(eforecastingdata),
        'eforecastingdata'[Date] <= MAX('eforecastingdata'[Date])
    ))

[v-rongtiep-msft]

Hi @jonbox ,

Does that make sense? If so, kindly mark my answer as the solution close the case and help others find the answer  please. Thanks in advance.

Best Regards

Community Support Team _ Polly

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

[v-rongtiep-msft]

Hi @jonbox ,

Please refer to my pbix file to see if it helps you.

Create a column firstly.

Short Month =
SWITCH (
    'Table (2)'[Date],
    "January", 1,
    "Febuary", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12,
    BLANK ()
)

Then Create a measure.

Measure = CALCULATE(SUM('Table (2)'[Actualprediction_USD]),FILTER(ALL('Table (2)'),'Table (2)'[Short Month]<=MAX('Table (2)'[Short Month])))

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@jonbox , You can consider suggestion of @truptis . But as you have a date, Create a date table; Join with date of your table and use month year on-axis from date table

Try measure like

total =
CALCULATE(
SUM( eforecastingdata[ActualPrediction_USD]),
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[jonbox]

Hi @amitchandak,

When you say create a table to organise date and join to existing table, how would i go about doing this?

[truptis]

Hi @jonbox ,

Create a rank (ID_MONTH) column for sorting it properly by month wise. Then use the below measure:

Quantity_CUMULATIVE =
CALCULATE (
[ActualPrediction_USD,
FILTER (
ALL ( Tablename[ID_MONTH] ),
Tablename[ID_MONTH]
<= MAX ( Tablename[ID_MONTH] ) - 1
)

)