Reporting Date , Exit Date , Join Date

DAX DAX Calculations
6

@Aree ,

This required a complete end-to-end approach. Here are the steps I took:

  1. added the Extended Date Table to the model, and marked it as a date table

  2. created the active and inactive relationships between the date table and the fact table to account for the three different dates

  3. created the DAX measures below

  4. dropped the measures into the table below

     Active Customers =
     CALCULATE( COUNTROWS( 'Data' ), 'Data'[Customer Status] = "Active" )

 Commencing Customers =
     VAR SelYr =
         SELECTEDVALUE( 'Dates'[Year] )
     VAR SelMo =
         SELECTEDVALUE( 'Dates'[MonthOfYear] )
     VAR Result =
         CALCULATE(
             COUNTROWS( 'Data' ),
             FILTER(
                 ALLSELECTED( 'Data' ),
                 YEAR( 'Data'[Extract_Date] ) = SelYr
                     && MONTH( 'Data'[Extract_Date] ) = SelMo
                     && YEAR( 'Data'[Commencement_Date] ) = SelYr
                     && MONTH( 'Data'[Commencement_Date] ) = SelMo
                     && 'Data'[Placement_Sequence_Number] = 1
             )
         )
     RETURN
         Result

 Exited Customers =
     CALCULATE( COUNTROWS( 'Data' ), 'Data'[Customer Status] = "Exited" )

 Drop Rate =
     DIVIDE( [Exited Customers], [Exited Customers] + [Active Customers], BLANK() )

Here’s what it looks like all put together:

image
image344×505 11.5 KB

I would strongly recommend going through the courses below, as they cover all the concepts I have utilized above in this solution.

image
image1394×455 99.1 KB

I hope this is helpful. Full solution file attached.

1 Like