Forum Discussion

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.

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      Contrary to natural language, " I want a and b" is expressed in boolean logic via OR  (||), not AND (&&)

       

       

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

       

      • ajdm2007's avatar
        ajdm2007
        Helper III
        1 year ago

        Hello lbendlin ,

         

        Thanks for your thought, I understand what you are saying.

         

        Let's do it this way:

         

        I have a formula that calculates the resolved tickets.

        total_tickets_solved = 
COUNTROWS(
    FILTER(
        tickets,
        RELATED('status'[description]) = "Completed"
    )
)

        This formula works smoothly as you can see.

         

        Now my next step is to calculate the number of solved tickets for the previous month. So adding the metric that already works and invoking the previous month function, the formula would be this:

        tmp = 
CALCULATE(
    [total_tickets_solved],
    PREVIOUSMONTH('calendar'[date])
)

         

        But it does not work, the result returns blank.

        So in my understanding this means that the problem is in the relationship between the ticket table and the calendar table.

         

        Any suggestions to confirm what I think?

         

        Thanks for any comments.