Comparing today value vs last day of the past month

[Anonymous]

Hi,

How can I compare todays value vs last day of the past month. For example, the percentage variation in a given time series value from last day of the past month (9/30/2019) against today (10/28/2019).

This is specifically to calculate the depreciation and appreciation of a currency over time.

Thanks in advance,

Ivan

[AlB · Accepted solution]

Hi @Anonymous 

Please always show a sample of your data model. Otherwise we have to guess, give a general answer and it's inefficient.

Please read through these posting tips (particularly the bits in red). By following them, you will increase the probability of getting your questions answered quickly and you will make things much easier for people trying to help.

You could create a measure and place it in a card visual:

Measure =
VAR TodayVal_ =
    CALCULATE (
        DISTINCT ( Table1[Value] ),
        FILTER ( ALL ( Table1[Date] ), Table1[Date] = TODAY () )
    )
VAR PreviousMonthVal_ =
    CALCULATE (
        DISTINCT ( Table1[Value] ),
        FILTER ( ALL ( Table1[Date] ), Table1[Date] = EOMONTH ( TODAY (), -1 ) )
    )
RETURN
    TodayVal_ - PreviousMonthVal_ //Simple subtraction. Change this to the operation that you need

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

Actually, it didn't work. 

It is returning all zeroes.

[Anonymous]

Thanks for the answer.

Also, if i would like to get the value for the end of last year, is there also a function?

[AlB]

@Anonymous 

If you want the 31st of December of the previous year, you could do:

Measure =
VAR TodayVal_ =
    CALCULATE (
        DISTINCT ( Table1[Value] ),
        FILTER ( ALL ( Table1[Date] ), Table1[Date] = TODAY () )
    )
VAR LastDayPreviousYearVal_ =
    CALCULATE (
        DISTINCT ( Table1[Value] ),
        FILTER (
            ALL ( Table1[Date] ),
            Table1[Date] = DATE ( YEAR ( TODAY () ) - 1, 12, 31 )
        )
    )
RETURN
    TodayVal_ - LastDayPreviousYearVal_ //Simple subtraction. Change to the operation you need

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

Maybe December 31st wont be the last working day. It could be a holiday or a sunday, so I would prefer a code that selects the last day available. Is this possible?