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 all,
I'm trying to graph the last value from each month. I've used the following script to make a column for last month values:
Column = VAR currentMonth = MONTH( Inventory[DateRelativeInv.Date] ) VAR latestDateofMonth = CALCULATE ( MAX ( Inventory[DateRelativeInv.Date] ), FILTER ( ALL (Inventory), MONTH( Inventory[DateRelativeInv.Date]) = currentMonth ) ) RETURN IF ( Inventory[DateRelativeInv.Date] = latestDateofMonth, 1, 0 )
However, it's only picking up max values from the last year, as opposed to last 2 years.
Anyone got any ideas?
Hi, your function seems to work great and saved my life hahaha
However, it was only checking the month of the date, so it was getting the maximum date of that month for any year. Example
31/01/2018 -> month 1 -> returns 31/01/2018
31/01/2017 -> month 1 -> returns 31/01/2018 (maximum date for month 1 in the column of dates)
I just changed a little and it's working perfectly for me:
VAR currentMonth =
MONTH( table[Date])
VAR currentYear =
YEAR( table[Date] )
VAR latestDateofMonth =
CALCULATE (
MAX ( table[Date] );
FILTER ( ALL(table); MONTH( table[Date]) = currentMonth && YEAR( table[Date]) = currentYear)
Let me know if you still have any trouble.
Hi @yathaj
Typically following pattersn is used to get clsoing month values
= CALCULATE ( SUM ( Inventory[Amount] ), ENDOFMONTH ( Date[Date] ) )
When I use this as a measure, duplicate values appear. Any way to get rid of those?
Thanks
Hi,
What is your end objective? Please describe the question in detail and also show the expected result. Why are you using a calculated column? May be your desired result can be obtained with a measure.
I'd like a graph simialar to this, except showing results from the last 24 months
Hi,
So you want to plot the last date of each of the 24 prior months on the X axis and then the value of each stack should the value as on that last day. Am i correct? If yes, then please share the link from where i can download your PBI file.
Hi,
That's correct.
Unfortunately I can't share the file as it has some confidential information. Is there any other info I can give you that will help you explain a solution?
Hi,
I will defeinitely need some data to work with. Create a dummy dataset
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |