Current month value using YTD minus previous month YTD

[metcala]

Hi

Sorry for the basic question.

I currently have a table with the following

Date	Unit	Activity	YTD
30/1/23	A	X	10
30/1/23	A	Y	15
30/1/23	A	Z	5
30/1/23	B	Y	20
30/1/23	B	Z	5
28/2/23	A	X	15
28/2/23	A	Y	25
28/2/23	A	Z	15
28/2/23	B	Y	25
28/2/23	B	Z	15

Currently trying to write a measure that will allow the following output

Date	Unit	Activity	Monthly Total
30/1/23	A	X	10
30/1/23	A	Y	15
30/1/23	A	Z	5
30/1/23	B	Y	20
30/1/23	B	Z	5
28/2/23	A	X	5
28/2/23	A	Y	10
28/2/23	A	Z	10
28/2/23	B	Y	5
28/2/23	B	Z	10

This is as far as I've got.... doesn't reflect unit/activity but couldn't even get previous month YTD working.

Monthly Total = CALCULATE(
SUM('Table'[YTD]),
FILTER('Table','Table'[Date]=EOMONTH('Table'[Date],-1))
)

Any help would be much appreciated!

[FreemanZ]

hi @metcala 

why Jan 30 not 31? what other values do you have for the date column?

[metcala]

@FreemanZ  Apologies this was just me adding the incorrect date for the example data.

[FreemanZ]

so the actual data is?

[metcala]

@FreemanZ As the data itself is sensitive I've anonymised the field names but the categories are the same as the original data.

[FreemanZ]

hi @metcala 

by actual data, i mean Jan 30 or 31?

[metcala]

Sorry it is always the end of the month so 31 Jan...my bad

[FreemanZ · Accepted solution]

or like:

Column2 = 
[YTD]-
CALCULATE(
    SUM(data[YTD]),
    data[date]=EOMONTH(EARLIER(data[date]), -1),
    ALLEXCEPT(data, data[Unit], data[Activity])
)

[metcala]

@FreemanZ 

Thank you very much for your help!

Much appreciated!

[FreemanZ]

hi @metcala 

it is always good to clarify first and no harm is done. 

please try to add a calculated column like:

Column = 
[YTD]-
MAXX(
    FILTER(
        data,
        data[date]=EOMONTH(EARLIER(data[date]), -1)
            &&data[Unit]=EARLIER(data[Unit])
            &&data[Activity]=EARLIER(data[Activity])
    ),
    data[YTD]
)

it worked like: