Dateadd not working with calculatetable

[AntonioFreitasW]

 Hi folks!

I need to find payments that exist in a specific month, but don´t appear in the following month.

I´m trying to create 2 dynamic tables with payments with due date on the context month and previous month, so I can use EXCEPT to find them. 
But when I try to create the table with payments due on the previous month, I still get only payments due on the context month...
Any idea what I´m doing wrong ? Here´s the DAX code:
    OnlyPrev =
       VAR CurrentMonth = MONTH(MAX(Calendar[Data]))
       VAR PrevMonth = MONTH(DATEADD(MAX(Calendar[Data]), -1, MONTH))
       RETURN
            CALCULATETABLE(
            PaymentTable,
            FILTER(
                  PaymentTable,
                  MONTH(PaymentTable[DueDate]) <> CurrentMonth &&
                  MONTH(DATEADD(PaymentTable[DueDate], -1, MONTH)) = PrevMonth
            )
        )

Thanks a lot for your help!

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[AntonioFreitasW]

Not sure how to paste excel here. I tried with copy and paste, but got an HTML error message...

[amitchandak]

@AntonioFreitasW , If dynamic means if should consider a filter or slicer, it will not. Tables are static 

You can try like

CALCULATETABLE(
PaymentTable,
FILTER(
PaymentTable,
EOMONTH(PaymentTable[DueDate],0) <> eomonth(today(),0) &&
EOMONTH(PaymentTable[DueDate],0) = eomonth(today(),-1)
)

The measure can be dynamic and you can use time intelligence for that

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[AntonioFreitasW]

Thanks a lot for the answer!
I tried your suggestion, but replacing TODAY() for the SELECTED Date, and it gave me a "Circular error" message...

 SoTemAntes =

      CALCULATETABLE(

      ContasaReceber,

      ALL(ContasaReceber),

      FILTER(ContasaReceber,

            EOMONTH(ContasaReceber[Data Vencimento],0) <> eomonth(SELECTEDVALUE(Datas[Datas]),0) &&

            EOMONTH(ContasaReceber[Data Vencimento],0) = eomonth(SELECTEDVALUE(Datas[Datas]),-1)

))
any idea what can be wrong ? 
thanks a lot for your help!

[amitchandak]

@AntonioFreitasW , Table are static, using selectedvalue will not result dynamic values

Use measures with help from Time intelligence

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[AntonioFreitasW]

Measures will give me single values right ?
That´s not what I´m looking for...
I need to generate a table with the customers´ payments that appear on one month and doesn´t appear on the next (selected). That´s why I need to generate a table.