Selecting the last value of last month

[Black_magic100]

I have a simple table that aggregates data each day.  I am trying to take a single value from this table that is from LAST month and I only want to SUM the LAST day of LAST month.  

Using this formula:

Invoices Coming Due Next Month Excl Past Dues = CALCULATE(SUM(ClubDayStats[Membershipdraftnextmonth_exclpastdue]), ENDOFMONTH(ClubDayStats[LogDate]))

...I am able to get it to work by using date filters (month offsets) or by simply applying a date to the visual so that it has context.

However, I now need a measure that already has this context and only shows last month. So for this month, I would need the value of february 28th because it is the LAST day of LAST month.  

[Anonymous]

Can you post some sample data?  If not, basically want to create a Calendar Table and then utilize the time-intelligence functions

[Black_magic100]

There is nowhere on this site to upload a file, but here is the sample data for last month and this month.  I just need February 28th

Logdate Membershipdraftnextmonth_exclpastdue
2019-02-01 2665.00
2019-02-02 4055.00
2019-02-03 5185.00
2019-02-04 8244.00
2019-02-05 16739.99
2019-02-06 18070.99
2019-02-07 19818.99
2019-02-08 21280.99
2019-02-09 23532.99
2019-02-10 24611.99
2019-02-11 25992.99
2019-02-12 27611.99
2019-02-13 29265.99
2019-02-14 30485.99
2019-02-15 32608.99
2019-02-16 34433.99
2019-02-17 36169.14
2019-02-18 37173.14
2019-02-19 40312.14
2019-02-20 41223.14
2019-02-21 43332.14
2019-02-22 45324.14
2019-02-23 45818.14
2019-02-24 47110.14
2019-02-25 48788.14
2019-02-26 49869.14
2019-02-27 55804.14
2019-02-28 55843.14
2019-03-01 4257.00
2019-03-02 5360.00
2019-03-03 6707.00
2019-03-04 10326.00
2019-03-05 18106.99
2019-03-06 18927.99
2019-03-07 20996.99
2019-03-08 23556.99
2019-03-09 25792.99
2019-03-10 27147.99
2019-03-11 28082.99
2019-03-12 30115.99

[Anonymous]

Not sure if this is what you had in mind (or something close).  

But putting Dates from the Calendar table on rows:

Total Membership Dues = SUM ( FactTable[Membershipdraftnextmonth_exclpastdue] )

LastDate Dues = 
CLOSINGBALANCEMONTH( 
    [Total Membership Dues], 
    DimCalendar[Date], 
    FILTER( DimCalendar, LASTDATE(DimCalendar[Date]) = DimCalendar[Date] )) //this is there because the figure will backfill into the month otherwise

This will provide the value at the end of each month.

[Black_magic100 · Accepted solution]

Okay so I was about to shoot myself trying to understand the DAX so naturally I turned to M/SQL to solve the issue.  Ended up creating a new column that would fill with the last day of last month for every single row in my date table. I then did a simple conditional column where if the date = isLastDayofLastMonth then isLastDayofLastMonth ELSE notLastDayofLastMonth.

I then just created a simple measure that filtered only those values!

= CALCULATE(SUM('ClubDayStats'[Membershipdraftnextmonth_exclpastdue]),'Calendar'[isLastDayofPreviousMonth] = "isLastDayofPreviousMonth")

[v-cherch-msft]

Hi @Black_magic100 

It seems you have resolved the issue, please mark your solution as answer to close this thread.

Regards,

Cherie

[Black_magic100]

hmmm not exactly.  I am sorry, but I left out the fact that there are 20 locations I am slicing by.  So I need to have the locations on the left (1 row per location) and then have the PurchaseAmount next to it (Already calculated), then I need the last day of last month's value (what I am trying to figure out right now with your help, and then lastly a simple subtraction between those two measures to get the difference.  

The purchase amount column changes daily and the other column would only update monthly.

[Black_magic100]

I have a calendar table.  

Here is my newest measure, but it returns nothing,

End of Last Month = CALCULATE(SUM('ClubDayStats'[Membershipdraftnextmonth_exclpastdue] ), LASTDATE(PREVIOUSMONTH('ClubDayStats'[LogDate])))

When I try to use time intelligence functions it never works.  I have spoken with so many people about it and there is nothing wrong with my date table.  I even made sure to declare it as the date table.