Forum Discussion

lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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
      Icon for Helper III rankHelper 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
        Icon for Super User rankSuper 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])
)