topic Re: Calculating Value Changes over Years and months with measures in DAX Commands and Tips

Calculating Value Changes over Years and months with measures

Hodgy007 — Wed, 02 Nov 2022 16:03:23 GMT

Ok on this face of it this looks easy buy finding it hard to do using measures, this is v simple in excel, but imagine this is 2 years of data.

Im looking to create a measure for Apples sold this year

and table of apples sold per month (Apples Month Diff)

Date	Apples in Stock
2022-01-03	100
2022-02-01	75
2022-03-01	60
2022-04-01	50
2022-05-02	41
2022-06-01	8

Re: Calculating Value Changes over Years and months with measures

v-yadongf-msft — Thu, 03 Nov 2022 09:32:35 GMT

Hi Hodgy007 ,

This is my test table:

Please create new columns as below:

Month = MONTH('Table'[Date]) Current month value = CALCULATE(SUM('Table'[Apples in stock]),FILTER('Table','Table'[Month] = EARLIER('Table'[Month]))) Last month value = CALCULATE(SUM('Table'[Apples in stock]),FILTER('Table','Table'[Month] = EARLIER('Table'[Month])-1)) Month Diff = IF('Table'[Last month value] <> BLANK(), [Current month value]-[Last month value])

Create a table visual, is this the result you want?

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculating Value Changes over Years and months with measures

Hodgy007 — Tue, 08 Nov 2022 10:02:05 GMT

Thanks, I was able to get this also using a measure not a column but how now would you calculate apples sold in the year to date? As due to the first 2 days not being in the date table and it not the end of the year. So startofyear and endofear date functions don't seem to work

Re: Calculating Value Changes over Years and months with measures

v-yadongf-msft — Wed, 09 Nov 2022 09:46:14 GMT

Hi Hodgy007 ,

I'm a little confused about what's the meaning of "the first 2 days not being in the date table and it not the end of the year", can you explain it in details ?

If convenient, could you please share with me some screenshots of your data after hiding sensitive information ?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculating Value Changes over Years and months with measures

Hodgy007 — Wed, 09 Nov 2022 09:51:13 GMT

The diff value of apples sold is in month 2 not month 1 so the charting is out by a month if you try and graph it so it looks like 227 were sold in feb not jan

Re: Calculating Value Changes over Years and months with measures

v-yadongf-msft — Fri, 11 Nov 2022 09:44:15 GMT

Hi Hodgy007 ,

Well, please create two columns:

Next month value = CALCULATE(SUM('Table'[Apples in stock]),FILTER('Table','Table'[Month] = EARLIER('Table'[Month])+1)) Month Diff = IF('Table'[Next month value] <> BLANK(), [Next month value] - [Current month value])

The diff value will show from Month 1.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.