Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Migrating a SQL query to Power Bi (retention rates)

Hello everyone. I started to migrate some Excel reports to Power BI and one of those was the Retention Rate. It was written in SQL and basically I have one table with all the information I want. The...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you, both of you. The pbix file can be downloaded here.

 

Basically this sample file only has the date when the order was placed and the user email (which was hashed). Also there's a column called frauduent, which should be filter as 0, pretty simple. 

 

Once again, thank you for your help guys! 

 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

Don't use a date hierarchy on the orderdatetime field. Instead, create a calculated column that only has the date value of orderdatetime, and then add a proper calendar table that holds the required granularity (day, month etc)

 

OrderDate = DATEVALUE(test[orderdatetime])
 
Calendar = CALENDARAUTO()
Month = MONTH('Calendar'[Date])
 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello lbendlin. 

     

    I fixed my sample file. Actually our original file has the data table like you said and I had forgotten to create the same table on this one. The link file is the same and can be downloaded here.

     

    Really hope you guys can help me on how to build the retention rate!