Get a previous month date in the context of a table visualisation

[davidlittle]

Hi, I have spent best part of a week trying to get this to work and it should be so simple so I know im doing something wrong. I have a table of orders that is taken as a snapshot from a live DB every day. I am using a table visualisation to filter to show just the snapshots on the first of every month. I then perform a count of all orders on that day that were "not in process" (Green box). I then want to "lookup" to what that figure was on the first day of the previous month. I have tried lots of eays of doing it using "EARLIER()", "FIRSTDATE(PREVIOUSMONTH())" and various incarnations of ALL() and ALLSELECTED(). And i cannot get it to work. My current incarnation allows me to specify a date manually and set all fields in the column to a previous month, but all the same previous month .. i want the previous month of each row... NB the Dates table is related only on "Snapshot Date"

[AlexisOlson · Accepted solution]

I'd expect something like this to be what you're after:

OutstandingLM =
VAR CurrDate = SELECTEDVALUE ( Dates[Date] )
VAR PriorDate = EOMONTH ( CurrDate, -2 ) + 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Orders Snapshot'[Order No] ),
        Dates[Date] = PriorDate
    )

[davidlittle]

You sir, are a god. I modified it slightly to be -2 in the EOMONTH func as otherwise it returned the same day, but it works! thank you!!

[AlexisOlson]

Yep. I already edited that after thinking about it more carefully.