Last value from each month

[yathaj]

 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?

[CaioMagan]

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.

[Zubair_Muhammad]

Hi @yathaj

Typically following pattersn is used to get clsoing month values

=
CALCULATE ( SUM ( Inventory[Amount] ), ENDOFMONTH ( Date[Date] ) )

[yathaj]

When I use this as a measure, duplicate values appear. Any way to get rid of those?

Thanks

[Ashish_Mathur]

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.

[yathaj]

I'd like a graph simialar to this, except showing results from the last 24 months

[Ashish_Mathur]

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.

[yathaj]

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?

[Ashish_Mathur]

Hi,

I will defeinitely need some data to work with.  Create a dummy dataset