YTD to Monthly Totals based on Unit

metcala — Wed, 02 Nov 2022 10:33:05 GMT

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!

Re: YTD to Monthly Totals based on Unit

Greg_Deckler — Wed, 02 Nov 2022 11:13:57 GMT

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 )

topic Re: YTD to Monthly Totals based on Unit in DAX Commands and Tips

YTD to Monthly Totals based on Unit

Re: YTD to Monthly Totals based on Unit