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.
Hi Folks
I have looked everywhere and get part answers but nothing that is working and need your help please.
I have a table that gives values for items against date/time. I need to report the change for each item per month.
I cannot use LastDate because there are duplicate dates against the same items (different times on same day)
I am struggling to get the last value for each item in a given period (from which I assume I can calculate the difference)
It is the change in order value per item per month that I am actually after.
Item | HistoricDate | ItemValue |
1 | 12/12/2019 13:11 | £0 |
1 | 12/12/2019 12:21 | £2,038,433.32 |
1 | 12/12/2019 10:06 | £2,066,400 |
1 | 30/10/2018 12:23 | £2,147,000 |
2 | 04/11/2020 15:05 | £50,960.83 |
2 | 05/10/2020 16:29 | £50,960.83 |
2 | 05/10/2020 16:26 | £101,921.67 |
3 | 11/08/2020 12:02 | £203,843.33 |
3 | 11/08/2020 12:02 | £407,686.66 |
3 | 12/12/2019 12:48 | £203,843.33 |
Your help would be gratefully appreciated
Solved! Go to Solution.
Hi, @NetBoulter
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file.
https://www.dropbox.com/s/ofuf9vqp5qt81jr/netb.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @NetBoulter
I am not sure if I understood your question correctly, but I see that item 3's august 2020 has the same day and the same time.
I cannot know which one is considered the last one.
I assume the sample is incorrect. Once it is corrected, please try the below.
Otherwise, sorry for the misunderstanding.
last value on last datetime of each month =
VAR currentitem =
MAX ( 'Table'[Item] )
VAR currentmonth =
EOMONTH ( SELECTEDVALUE ( 'Table'[HistoricDate] ), 0 )
VAR lastdatetimeeachmonth =
CALCULATE (
MAX ( 'Table'[HistoricDate] ),
FILTER (
ALL ( 'Table' ),
'Table'[Item] = currentitem
&& EOMONTH ( 'Table'[HistoricDate], 0 ) = currentmonth
)
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[HistoricDate] ) = lastdatetimeeachmonth,
SUM ( 'Table'[ItemValue] ),
0
)
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
So what it seems to be doing is adding all the activity. For item 1 - I need it to recognise the first change as +2,147,000 then the second change as -108,566.68
Sorry - wrong picture!!
This what I am getting ....
Hi, @NetBoulter
It will be very much helpful for me to fix the measure if I can know what you want to see.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you .. To clarify - the table published shows the same date/time but in fact the seconds are different.
I tried this but am getting the following:
Hi, @NetBoulter
Thank you for your feedback.
What are you trying to see inside the Matrix visualization?
My measure is only showing the value if it is the last value of each month, otherwise zero.
Please describe your expected outcome in the matrix visualization.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi
This what I am trying to get ....
Hi, @NetBoulter
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file.
https://www.dropbox.com/s/ofuf9vqp5qt81jr/netb.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
97 | |
95 | |
76 | |
72 | |
65 |
User | Count |
---|---|
140 | |
109 | |
103 | |
82 | |
73 |