Forum Discussion

wollensack's avatar
wollensack
Frequent Visitor
3 years ago

Measure to Count Records from Previous Month

Hello All,


I am trying to calculate the total records from the previous month.
I looked into a similar solution to my needs, but it did not help
 https://community.powerbi.com/t5/Desktop/Create-a-measure-that-shows-me-the-previous-month/m-p/605919#M288486


All I want to do is create 2 CARDS
If my Slicer value is 01/01/2023, I want to see:
Card 1 = 4 (Current Month Count) (I can do this one)
Card 2 = 5 (Previous Month Count)

Table = Test1

Report_MonthJob_Nbr
1/01/20231251216
1/01/20231110997
1/01/20231044340
1/01/20231155551
1/12/20221048804
1/12/20221253120
1/12/20221290105
1/12/20221175991
1/12/20221285373
1/11/20221137240


I have also tried using these this measure as well
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Calc_Prev_Count =
Var Latest_M = MAX('Slicer_Month'[Report Month])
Var Current_Month_Count = CALCULATE(DISTINCTCOUNT('Test1'[Job_Nbr]),'Test1'[Report Month] = Latest_M)
Var Prvious_Month_Count = CALCULATE(DISTINCTCOUNT('Test1'[Job_Nbr]),'Test1'[Report Month] = EDATE(Latest_M,1))
Var Change_Value = 0 + Current_Month_Count - Prvious_Month_Count
Return Change_Value
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Thankyou
Frank

Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello,

     

    Please try using this format,

     

    Prev Month Count=CALCULATE( COUNT('Table'[Column]), DATESYTD('Table'[Date]) = DATEADD(DATESYTD('Table'[Date]), -1, MONTH), SAMEPERIODLASTYEAR('Table'[Date]) )

     

    Thanks,

    • wollensack's avatar
      wollensack
      Frequent Visitor
      3 years ago

      Hello Sanika,

      thankyou for replying to my message
      I assumed your solution is only looking at the one table "Table1"
      I tried your solution and got an error

      Prev Month Count = CALCULATE( COUNT('Table1'[Order_Nbr]), DATESYTD('Table1'[Date]) = DATEADD(DATESYTD('Table1'[Date]), -1, MONTH), SAMEPERIODLASTYEAR('Table1'[Date]) )

      Error = "A function 'DATESYTD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."