Count Year to Date and LTM

[PeterStuhr]

Hi,

I am fairly new to Power BI and DAX in general.

I have a table with Opportunities, containg a "status" field "Won/Lost/Open".

I would like to calculate the Winrate (Count of Won opps / Count of Won+Lost Opps). I would like this for "Year to date" and "Last 12 months".

Any advice would be appreciated!

[AlB]

Hi @PeterStuhr 

You need to show the structure of your tables and sample data to help people trying to answer. Otherwise they have to make guesses. Please show your sample data in text-tabular format in addition to (or instead of) the screen captures. Just use 'Copy table' in Power BI and paste it here.

Do you have a date table? Is there a date column in your table?

'Year to date' for what year, until what date?' 'Last 12 months' from when?

[PeterStuhr]

Hi @AI 

Thanks for your comment.

I hope this is what you mean:

Opportunity	Status	CreateDate	CreateMonth	CreateYear	CreateYearMonth	CloseDate	CloseMonth	CloseYear	CloseYearMonth
XA	Won	01-01-2017	1	2017	2017-1	31-01-2017	1	2017	2017-1
XB	Won	02-01-2017	1	2017	2017-1	01-02-2017	2	2017	2017-2
XC	Open	03-01-2017	1	2017	2017-1
XD	Lost	04-01-2017	1	2017	2017-1	03-02-2017	2	2017	2017-2
XE	Lost	05-01-2017	1	2017	2017-1	04-02-2017	2	2017	2017-2
XF	Won	06-02-2017	2	2017	2017-2	08-03-2017	3	2017	2017-3
XG	Lost	07-02-2017	2	2017	2017-2	09-03-2017	3	2017	2017-3
XH	Won	13-02-2017	2	2017	2017-2	15-03-2017	3	2017	2017-3
XI	Lost	20-02-2017	2	2017	2017-2	22-03-2017	3	2017	2017-3
XJ	Won	15-02-2017	2	2017	2017-2	17-03-2017	3	2017	2017-3

it looks similar to this.

"Year To date" should be Jan-1 till Today, and "Last 12 months" should be from Feb15-18 till today. So should always be real time, if that makes sense.

[AlB]

@PeterStuhr 

I guess you want to take into account the CloseDate to determine the month in which you count the opportunity. Otherwise you can just change Table1[CloseDate] for Table1[CreateDate] in the code below.  You can create two measures as follows and place them, for instance, in a card visual. Table1 is the table you show.

Last12MonthsMeasure =
CALCULATE (
    DIVIDE (
        CALCULATE ( COUNT ( Table1[Status] ), Table1[Status] = "Won" ),
        CALCULATE ( COUNT ( Table1[Status] ), Table1[Status] IN { "Won", "Lost" } )
    ),
    Table1[CloseDate] <= EDATE ( TODAY (), -12 )
)

Year2DateMeasure =
CALCULATE (
    DIVIDE (
        CALCULATE ( COUNT ( Table1[Status] ), Table1[Status] = "Won" ),
        CALCULATE ( COUNT ( Table1[Status] ), Table1[Status] IN { "Won", "Lost" } )
    ),
    YEAR ( Table1[CloseDate] ) = YEAR ( TODAY () )
)

You might have to use ";" instead of  "," in the code above depending on your OS locale settings

[PeterStuhr]

Hi @AlB 

Thanks a lot. I can make the YTD one work.

Unfortunately, my "Last 12 months" seems to calculate a bit wrong compared to when I do it manually. I cant seem to find the reason.. I did this:

WinRate LTM = CALCULATE ( DIVIDE ( CALCULATE ( COUNT ( CRMOpportunities[OpportunityStatus] ); CRMOpportunities[OpportunityStatus] = "Won" ); CALCULATE ( COUNT ( CRMOpportunities[OpportunityStatus] ); CRMOpportunities[OpportunityStatus] IN { "Won"; "Lost" } ) ); CRMOpportunities[OpportunityCloseDate] <= EDATE(TODAY();-12))

[AlB · Accepted solution]

@PeterStuhr 

Mistake in the code. Needs >= instead of <=

Last12MonthsMeasure =
CALCULATE (
    DIVIDE (
        CALCULATE ( COUNT ( Table1[Status] ), Table1[Status] = "Won" ),
        CALCULATE ( COUNT ( Table1[Status] ), Table1[Status] IN { "Won", "Lost" } )
    ),
    Table1[CloseDate] >= EDATE ( TODAY (), -12 )
)

.