Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX-How to Count Month Basis Value

I would like to count the  count of ticket Closed Last Month and Current Month   (Count of Tickets closed Closed Date <  Due Date) / Count of TicketID ),

Kindly find the Below Table 

 

 

i have used 

LastMonthClosed = CALCULATE(Table[ClosedDate] < Table[DueDate] / COUNT(Table[TicketID]),Filter(Table,IF(Table[Type]=="Account" && MONTH(TODAY()-1),0))
 
  • vivran22's avatar
    vivran22
    6 years ago

    Anonymous 

     

    Created the calendar table and relate it with Completed date. Then use following measures:

     

    Tickets Closed = 
    COUNTROWS(dtTable)

Ticket Closed (Before Due Date) = 
    CALCULATE(
        [Tickets Closed],
           FILTER(
                dtTable,
                dtTable[ClosedDate] < dtTable[DueDate]
           )
    )




Ticket Closed (Prev Month) = 
CALCULATE(
    [Tickets Closed],
    PREVIOUSMONTH(ftCalendar[Date])
)

Ticket Closed (Before Due Date, Prev Month) = 
CALCULATE(
    [Ticket Closed (Before Due Date)],
    PREVIOUSMONTH(ftCalendar[Date])
)


% Closed (CM) = DIVIDE([Ticket Closed (Before Due Date)],[Tickets Closed])

% Closed (PM) = DIVIDE([Ticket Closed (Before Due Date, Prev Month)],[Ticket Closed (Prev Month)])

     

     

    Sol pbix file here

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
    If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

    Blog: vivran.in/my-blog
    Connect on LinkedIn
    Follow on Twitter

9 Replies

  • vivran22's avatar
    vivran22
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hello Anonymous ,

     

    You may try this:

     

    Sample Data table:

    Add a calendar table:

    ftCalendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",YEAR([Date]),
    "Month",EOMONTH([Date],-1)+1,
    "QTR","Q" & FORMAT([Date],"Q")
)

     

    You may refer to the article for more details: Calendar Table

     

    Create relationships between the data table and calendar table

    Add following measures:

     

    Ticket Created (Current Month) = 
    COUNTROWS(dtTable)

Tickets Closed = 
CALCULATE(
    [Ticket Created (Current Month)],
    USERELATIONSHIP(ftCalendar[Date],dtTable[ClosedDate])
)


Ticket Closed (Prev Month) = 
CALCULATE(
    [Tickets Closed],
    PREVIOUSMONTH(ftCalendar[Month])
)


Exceeding Due Date = 
VAR _Filter = 
    CALCULATETABLE(
        FILTER(
        dtTable,
        dtTable[DueDate] < dtTable[ClosedDate]),
            USERELATIONSHIP(ftCalendar[Date],dtTable[DueDate])
        )
VAR _Count = 
        COUNTROWS(_Filter)

RETURN
_Count

     

    You will get the following result

     

    Solution PBIX file here

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution. Kudos would be a cherry on the top :)(Hit the thumbs up button!)
    If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

    Visit blog: vivran.in/my-blog

    Feel free to email me for any BI needs .

    Connect on LinkedIn
    Follow on Twitter

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      vivran22    I'm not able open your PBIX file, showing some error

       

      • vivran22's avatar
        vivran22
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

         

        Ideally, it should open as I can access the file using the same link.

         

        Reposting the link

        Sol file

         

        Cheers!
        Vivek

        If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
        If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

        Feel free to email me for any BI needs.
        Blog: vivran.in/my-blog
        Connect on LinkedIn
        Follow on Twitter