DAX formula

[jimpatel]

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

[v-yiruan-msft · Accepted solution]

@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

[v-yiruan-msft · Accepted solution]

@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

[jimpatel]

Thanks a lot for your input.

Any help in getting this for 6 months rolling please?

Thanks a lot

[jimpatel]

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

[jimpatel]

Great!

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

Thanks a lot

[ValtteriN · Accepted solution]

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?

[ValtteriN]

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/

[jimpatel]

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