topic Re: DAX-How to Count Month Basis Value in DAX Commands and Tips

DAX-How to Count Month Basis Value

Anonymous — Sun, 31 May 2020 02:32:41 GMT

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

Re: DAX-How to Count Month Basis Value

vivran22 — Sun, 31 May 2020 03:34:18 GMT

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

Re: DAX-How to Count Month Basis Value

Anonymous — Mon, 01 Jun 2020 04:59:21 GMT

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

Re: DAX-How to Count Month Basis Value

vivran22 — Mon, 01 Jun 2020 05:47:16 GMT

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

Re: DAX-How to Count Month Basis Value

Anonymous — Wed, 03 Jun 2020 06:12:00 GMT

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)

Re: DAX-How to Count Month Basis Value

vivran22 — Wed, 03 Jun 2020 11:31:19 GMT

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

Re: DAX-How to Count Month Basis Value

Anonymous — Wed, 03 Jun 2020 12:28:25 GMT

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)

Re: DAX-How to Count Month Basis Value

vivran22 — Wed, 03 Jun 2020 14:37:29 GMT

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

Re: DAX-How to Count Month Basis Value

Anonymous — Wed, 03 Jun 2020 16:59:16 GMT

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)

Re: DAX-How to Count Month Basis Value

vivran22 — Thu, 04 Jun 2020 06:53:31 GMT

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