Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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.
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.
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.
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |