Aggregate last month value to next monrth

[Tanaka]

Hello,

i've the follow table

	January	February	March
Sales	5	6	282
Cancelled	3	6	19
SubTotal	2	0	263
Total

I'm trying to sum subtotal of the last month with the next one, already tried this measure, but it returns blank rows.

Previous =
CALCULATE(

Table1[Subtotal]

,PREVIOUSMONTH(Table1[Date])

)

The desired output would be as follows.

	January	February	March
Sales	5	6	282
Cancelled	3	6	19
SubTotal	2	0	263
Total	2	2 (this month Subtotal + last month Total)	265 (this month Subtotal + last month Total)

Any ideas?

Regards.

[PaulDBrown]

Can you please post sample data (you can recreate the table structure + data in Excel) replicating the structure of your data?

[Tanaka]

Sure.

https://drive.google.com/drive/folders/1yx52ipbehpIbGb-F7WkRVCXxktQZPHoD?usp=sharing

[PaulDBrown · Accepted solution]

See if this works. I've create a Date Table in the model

With this measure:

Previous SubTotal  + Total =
CALCULATE (
    [Subtotal],
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] <= MAX ( 'Date Table'[Date] )
    )
)

 I've attached the sample PBIX file

[Tanaka]

Nice !! =D

Thank you ! !

[PaulDBrown]

What is the actual structure of the data? Does the data include dates? Do you have a Date Table?

[Tanaka]

I don't have a date table.

I do have a date column.
I distinct count  IDs to get Sales, and for the cancelled ones i distinct count filtering where status is cancelled.
Subtotal is just the substraction of both results.