Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Measure to get previous month data

I have a query could you pls help me to get this sorted. Have a slicer where it contains month as 22-08, 22-09, 22-10, 22-11, 22-12. Based on the slicer value selection I have to always get previous month data in the card. 
I tried using this measure but it only gives previous month data that is Nov month only but when I select 22-11 it should show Oct month data in the card.

CALCULATE(count(NGRReport[reportid]) ,MONTH(NGRReport[createdon])=(MONTH(TODAY())-1))

And the 2nd card which I have as current month data should show Nov month value when I select 22-11 in slicer

 

 

Could you pls help me to get this sorted.
Need Help

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    You must use YearMonth format, and you cannot do math like that. MONTH(TODAY())-1 will fail in January.

     

    Use PREVIOUSMONTH or DATEADD built-in functions.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    lbendlin  I have tried using PREVIOUSMONTH function but dint get any data instead got Blank.

    Slicer is in Year-month format (22-10). Used below measure, could you pls help me to correc it. Thanks in advance. 

    CALCULATE(COUNT(NGRReport[reportid]), PREVIOUSMONTH(NGRReport[createdon]))

    NGRReport[createdon] is in 08/26/2022 format which is been used in the measure & the slicer value is 22-10.
    User wants the slicer to be in 22-10 format itself. 

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      Sounds like your data model is missing the calendar table? These functions require a correctly set up and linked calendar table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        I have the Calendar table linked with NGRReport table which is been used in the measure.
        I tried taking Created month column as slicer value in date format & got the expected results using PREVIOUSMONTH. But one issue is when the slicer is selected as ALL it shows same values in current month, overall report generated & blank in previous month card.
        Could you pls help how to sort this.

        Always the slicer selection should be as ALL & there are other visuals in the report to show data as Overall.