topic Re: Current month value using YTD minus previous month YTD in DAX Commands and Tips

Current month value using YTD minus previous month YTD

metcala — Thu, 11 May 2023 11:28:30 GMT

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!

Re: Current month value using YTD minus previous month YTD

FreemanZ — Thu, 11 May 2023 12:12:14 GMT

hi metcala

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

Re: Current month value using YTD minus previous month YTD

metcala — Thu, 11 May 2023 12:13:32 GMT

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

Re: Current month value using YTD minus previous month YTD

FreemanZ — Thu, 11 May 2023 12:15:49 GMT

so the actual data is?

Re: Current month value using YTD minus previous month YTD

metcala — Thu, 11 May 2023 12:18:06 GMT

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

Re: Current month value using YTD minus previous month YTD

FreemanZ — Thu, 11 May 2023 12:53:27 GMT

hi metcala

by actual data, i mean Jan 30 or 31?

Re: Current month value using YTD minus previous month YTD

metcala — Thu, 11 May 2023 13:12:17 GMT

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

Re: Current month value using YTD minus previous month YTD

FreemanZ — Fri, 12 May 2023 01:44:20 GMT

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:

Re: Current month value using YTD minus previous month YTD

FreemanZ — Fri, 12 May 2023 01:46:51 GMT

or like:

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

Re: Current month value using YTD minus previous month YTD

metcala — Fri, 12 May 2023 07:11:58 GMT

FreemanZ

Thank you very much for your help!

Much appreciated!