Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Weird Previousmonth behavior

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?

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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 )
    )

     

     

     

     

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-frfei-msft
        Community Support
        6 years ago

        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 )
    )