Dax for finding conversion rate

[Anonymous]

Hello altruist,

I need to find the inquiry to the application conversion rate for the current week in comparison to the inquiry in the same week last month. Here's a snapshot of the source table and the target table. Does anyone have any idea how to find the conversion rate?

Thanks

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. 

1. Create the two calculated columns as below to get the special week number

StartdayofWeek = 'Table'[Date]
                    + -1
                        * IF (
                            WEEKDAY ( 'Table'[Date] ) < 6,
                            WEEKDAY ( 'Table'[Date] ) + 1,
                            WEEKDAY ( 'Table'[Date] ) - 6
                        )

Week = 
1 + WEEKNUM ( 'Table'[StartdayofWeek], 1 )
    - WEEKNUM ( STARTOFMONTH ( 'Table'[StartdayofWeek] ), 1 )

 

2. Create a measure as below to get the conversion rate

Conversion rate = 
VAR _appcount =
    SUM ( 'Table'[Application Count] )
VAR _premonthinqcount =
    CALCULATE (
        SUM ( 'Table'[Inquiry Count] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[Date] ) = SELECTEDVALUE ( 'Table'[Date].[Year] )
                && MONTH ( 'Table'[Date] )
                    = SELECTEDVALUE ( 'Table'[Date].[MonthNo] ) - 1
                && 'Table'[Week] = SELECTEDVALUE ( 'Table'[Week] )
        )
    )
RETURN
    DIVIDE ( _appcount, _premonthinqcount, 0 )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@Anonymous ,  Creat a date table with the week

Create a Friday week from here

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

Then

you need measures

Inquiry = Sum(Table[Count of Inquiry]

Applications= Sum(Table[Application Count]

Conversion = divide([Inquiry ],[Applications])

refer if needed

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8