DAX-How to Count Month Basis Value

[Anonymous]

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))

@nandukrishnavs 

[vivran22]

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]

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

[vivran22]

@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

[Anonymous]

@vivran22

Hi,

Kindly Download the Sample data

Condition:   Last Month = if(type="Account") and (Closed Date < Request Due Date) / (Count of Tickets Closed last Month)

CurrentMonth =   if(type="Account") and (Closed Date < Request Due Date) / (Count of Tickets Closed Current Month)

[vivran22]

@Anonymous 

Follow the calendar creation process and link it with the data table as suggested in the post earlier.

Then use the following measures:

Ticket Closed (Current Month) = 
VAR _Filter = 
     FILTER(
                dtTable,
                dtTable[Type] = "Account"
                    && dtTable[ClosedDate] < dtTable[DueDate]
            )
VAR _Count = 
    CALCULATE(
        COUNTROWS(dtTable),
           _Filter
    )

RETURN
_Count


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

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

[Anonymous]

@vivran22 

it's Not working  kindly find below condition

Last Month = if(type="Account") and (Closed Date < Request Due Date) / (Count of Tickets Closed last Month)

CurrentMonth =   if(type="Account") and (Closed Date < Request Due Date) / (Count of Tickets Closed Current Month)

[vivran22]

@Anonymous 

It is not clear what do you mean by "/" in you condition.

Can you tell me the expected output (in a form of table) from the example dataset you have shared?

Cheers!
Vivek

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

[Anonymous]

@vivran22 

Kindly Download  the Sample Result File (i have mentioned conditions and sample Results)

Condition:

Current Month = (if type=Account and (Closed date < Due date) divided by no of Ticket ID closed by Current Month)

Last Month = (if type=Account and (Closed date < Due date) divided by no of  Ticket ID closed by Last Month)

[vivran22 · Accepted solution]

@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