Weird Previousmonth behavior

[Anonymous]

Hi Guys,

I am trying to do a Count of X (submit dates) of the previous month. 
I have a date table linked to the dates I am interested in in the fact table: the relationship is okay and I can drag and drop dates from the date table into the canvas, with the corresponding submit dates.

The functions Dateadd and previousmonth are both not working though.

The workaround is working, which is:
Incoming Previous Month =

CALCULATE(COUNT(Query1[ID]);

MONTH('Date'[Date]) = MONTH(TODAY())-1 &&

YEAR('Date'[Date]) = YEAR(TODAY())

)

Whenever I try to use PREVIOUSMONTH, the result is blank. When I put a relative date slicer in the canvas and tell it to slice for the last calendar month, PREVIOUSMONTH is doing a correct count, but for the month of july.

What to do to get it to work?

[v-frfei-msft]

Hi @Anonymous ,

One sample for your reference. We should add the date column of the fact table to the visual to work on it. Then both Dateadd and PREVIOUSMONTH work well.

Measure = CALCULATE(SUM('Table'[value]),PREVIOUSMONTH('Table'[date]))

Pre = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date],-1,MONTH))

Pbix as attached.

[Anonymous]

Thank you very much for the reply, I see it works like a charm.

I think it was a misunderstanding from my part: I wanted to show the amount belonging to the previous month on a Card visual (last month's count). In your example, using the measure in a card visual also returns a blank.

How do I incorporate these measures in a card visual? So that one amount (that of the last month) is shown?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

In your scenario, we cannot use previousmonth or dateadd. As for both fucntion, we can get the date by the current row, However in a card visual, there is no filter like a table visual. That's why we got incorrect result by that way. So we can use EDATE to work on it that the logic is slmilar to your work around.

previous =
VAR a =
    FORMAT ( EDATE ( TODAY (), -1 ), "yyyymmm" )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( 'Table', FORMAT ( 'Table'[date], "yyyymmm" ) = a )
    )

[amitchandak]

Please check the explanation given  on previousmonth at

https://community.powerbi.com/t5/Desktop/PREVIOUSMONTH/td-p/13307.

Hope that will help.