topic Re: DAX formula in DAX Commands and Tips

DAX formula

jimpatel — Thu, 09 May 2024 10:58:00 GMT

Thanks for looking at my post.

I have below data and looking for some formula to replicate "Final result" column please. Any idea will be appreciated.

I am looking for some DAX formula for counting number of "twos" per month and converting to percentage please.

In this instance, for the month of Jan it will be 3/13 will be 23.07%. for month of Feb it will be 0% as there is no twos in that month. For month of March it will 7/10 and answer will be 70%.

Note : Date column wont be in order and it will be mixed with several dates. I have put it in ascending for better understanding.

Any idea will be much appreciated

Thanks a lot

Re: DAX formula

ValtteriN — Thu, 09 May 2024 11:43:30 GMT

Hi,

Here is one way to do this:

Data:

Relation:

Dax:

Measure 27 =

var _month = MONTH(MAX('Calendar'[Date]))

var _year = year(MAX('Calendar'[Date]))

var _2s = COUNTROWS(FILTER(ALL('Table (38)'),'Table (38)'[Value]=2 && MONTH('Table (38)'[Date])=_month && year('Table (38)'[Date])=_year))

var _all = COUNTROWS(FILTER(ALL('Table (38)'), MONTH('Table (38)'[Date])=_month && year('Table (38)'[Date])=_year))

RETURN

DIVIDE(_2s,_all)

End result:

Note That the table in visual is using 'calendar'[date]

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: DAX formula

jimpatel — Thu, 09 May 2024 12:06:07 GMT

Great!

Thanks a lot for your reply. Is it possible to use DAX column formula instead of measure pleasE?

Thanks a lot

Re: DAX formula

jimpatel — Thu, 09 May 2024 15:04:43 GMT

Thanks a lot for your reply. For some reason the answer i am getting is wrong. Do i need to tweak anything in the formula please?

thanks a lot again

Re: DAX formula

Anonymous — Fri, 10 May 2024 06:42:04 GMT

ValtteriN Thanks for your contribution on this thread.

Hi jimpatel ,

You can create a measure as below to get it, please find the details in the attachment.

Final result = VAR _date = SELECTEDVALUE ( 'Table'[Due Date] ) VAR _partmonth = CALCULATE ( COUNT ( 'Table'[Due Date] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR('Table'[Due Date])=YEAR(_date) && MONTH ( 'Table'[Due Date] ) = MONTH ( _date ) && 'Table'[Time to Close] = 2 ) ) VAR _month = CALCULATE ( COUNT ( 'Table'[Due Date] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR('Table'[Due Date])=YEAR(_date) && MONTH ( 'Table'[Due Date] ) = MONTH ( _date ) ) ) RETURN IF ( ISBLANK ( _partmonth ), 0, DIVIDE ( _partmonth, _month ) )

Best Regards

Re: DAX formula

ValtteriN — Fri, 10 May 2024 15:27:24 GMT

Hi jimpatel ,

The dax I used should work also in column with small changes:

Column =

var _month = MONTH([Date])

var _year = year([Date])

var _2s = COUNTROWS(FILTER(ALL('Table (38)'),'Table (38)'[Value]=2 && MONTH('Table (38)'[Date])=_month && year('Table (38)'[Date])=_year))

var _all = COUNTROWS(FILTER(ALL('Table (38)'), MONTH('Table (38)'[Date])=_month && year('Table (38)'[Date])=_year))

RETURN

DIVIDE(_2s,_all)

End result:

Could you describe what kind of issue you encountered with this approach?

Re: DAX formula

jimpatel — Mon, 20 May 2024 14:47:19 GMT

Thanks a lot for your kind help.

It works well and thanks a lot for that.

One last question, how can i get last 6 months (rolling) in this formula please?

Thanks a lot again

Re: DAX formula

jimpatel — Mon, 03 Jun 2024 17:20:21 GMT

Thanks a lot for your input.

Any help in getting this for 6 months rolling please?

Thanks a lot