Forum Discussion

ajdm2007's avatar
ajdm2007
Helper III
1 year ago
Solved

Adding previous month measure

Hi everyone,

 

I have this visualization with several metrics. I’d like to add a metric to each card showing the value from the previous month. For example, if I'm viewing the data for August, I want to add a metric at the bottom showing the value for the previous month, along with the percentage difference next to it.

 

The first step would be to create the metric for the previous month. I understand that Power BI has a function called PREVIOUSMONTH, but I'm not entirely sure how to use it.

 

I have this formula already:

total_tickets_solved_previous_month =
CALCULATE(
    COUNTROWS(
        FILTER(
            tickets,
            RELATED('status'[Description]) = "Completed"
        )
    ),
    PREVIOUSMONTH('calendar'[Date])
)
 

But the result I’m getting is coming up blank.

 

The calendar table and the tickets table are related as needed for these cases.

 

 

 

 

How can I do that?

 

Thank you guys.

 

 

 

General Comment
Need Help
Tips and Tricks

21 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Formula looks ok.  Did you mark your Calendar table as a Date table?  Can you confirm you have data for the previous month for that Description filter?

    • ajdm2007's avatar
      ajdm2007
      Helper III
      1 year ago

      Hello lbendlin,

       

      Yes, my calendar table is my date table.

       

       

      calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2023,01,01),DATE(2030,12,31)),
    "Year", YEAR([DATE]),
    "Month", MONTH([DATE]),
    "Quarter", FORMAT([DATE], "\QQ"),
    "MonthName", FORMAT([DATE], "MMM"),
    "WeekDay", FORMAT([DATE], "dddd"),
	"DayNumberOfWeek", SWITCH(TRUE(),WeekDay([Date],2) = 1, 1, WeekDay([Date],2) = 2, 2,WeekDay([Date],2) = 3, 3,WeekDay([Date],2) = 4, 4,WeekDay([Date],2) = 5, 5,WeekDay([Date],2) = 6, 6,WeekDay([Date],2) = 7, 7),
    "Day", DAY([DATE]),
    "FirstMonthDay", FORMAT(EOMONTH([DATE], -1) + 1,"mm/dd/yyyy"),
	"FiscalMonth", IF(MONTH([DATE]) >= 7, MONTH([DATE]) - 6, MONTH([DATE]) + 6)
	)

       

       

       

       

      Indeed, I do have data for the previous month.

       

      I was thinking about something.

       

      In the ticket table, the status column can contain the value >Complete* or >Complete (No Email), so my understanding is that when the formula specifies:
      RELATED('status'[description]) = "Completed", it's searching the ticket table for all records where the status is either >Complete* or >Complete (No Email). Am I wrong?

       

      Thanks for your help.

      • ajdm2007's avatar
        ajdm2007
        Helper III
        1 year ago

        Guys,

        I think the problem is not in the relationship, I have tried this formula and it didn't work either:

        tmp = 
CALCULATE(
    COUNTROWS(
        FILTER(
            tickets,
            tickets[status]=">Completed*" && tickets[status]=">Completed (No email)"
        )
    ),
    PREVIOUSMONTH('calendar'[date])
)

         

        Unless the issue is in the relationship of the calendar table.

         

        Any idea?

         

        Thank you so much.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    There seems to be a problem with the relationship as seen in the second screenshot.  Check the columns which you are releating to on the One side of the relationship.

    • ajdm2007's avatar
      ajdm2007
      Helper III
      1 year ago

      Hello Ashish_Mathur

       

      Thank you for your reply, but I'm not sure what you mean. Can you tell me where are you whatching the issue on the relationship?

       

       

      Thank you, 

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        1 year ago

        You are welcome.  In the first post, you were linking to the ID column of the Status table but in the second one you are connecting to the status column of the status table.  I am confused.  Share the download link of the PBI file and show the problem there clearly.