YTD to Monthly Totals based on Unit

[metcala]

Hi

I am pretty new to DAX and am just looking for pointers to add a new column which pulls out the monthly totals by unit.

Month	Unit	YTD
31/01/2022	A	50
31/01/2022	B	100
28/02/2022	A	70
28/02/2022	B	120
31/03/2022	A	100
31/03/2022	B	160
30/04/2022	A	130
30/04/2022	B	200

I have managed to use the following for coverting YTD to monthly without the Unit but really struggling with looking up the unit. Had a look into LOOKUPVALUE and can't seem to get it working.

Column =

VAR pv =

CALCULATE (
   MAX(Sales[YTD],

   Sales[Month] < EARLIER(Sales[Month]),

   REMOVEFILTERS(Sales)

)

IF(MONTH(Sales[Month]) = 1, Sales[YTD],

   Sales[YTD] - pv

)

Any help or guidance would be really appreciated!

[Greg_Deckler · Accepted solution]

@metcala So like this?

Column =

VAR pv =

CALCULATE (
   MAX(Sales[YTD],

   Sales[Month] < EARLIER(Sales[Month]) && Sales[Unit] = EARLIER(Sales[Unit]),

   REMOVEFILTERS(Sales)

)

IF(MONTH(Sales[Month]) = 1, Sales[YTD],

   Sales[YTD] - pv

)