Forum Discussion
Weird Previousmonth behavior
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 ) )
Community SupportHi 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.
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
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 ) )