Forum Discussion

hanlind's avatar
hanlind
Frequent Visitor
9 years ago

Tables with multiple date fields for a unique record.

I am trying to measure the number of homes in a table that were licensed and closed, by month and year.

 

I would like to show a trend of the number of homes that were licensed in a given month, year, or quarter on one bar.  On the next bar I would like to show the number of homes that were closed in the same timeframe.

 

What I am getting is the number of homes licensed in one bar, and in the second bar the number of those homes that have been closed...no matter which month or year the closure occured.

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi hanlind

     

    Please share data and the output expected given that data.  Put it on OneDrive or DropBox and share the link.

     

    Cheers

     

    CheenuSing

    • hanlind's avatar
      hanlind
      Frequent Visitor
      9 years ago
      StateHOMEIDHOMENAMELICENSED_DATECLOSED_DATE
      South Carolina1005Home52/17/19995/19/2016 0:00
      Ohio1001Home11/25/19998/25/2016 0:00
      Indiana1002Home21/28/20009/26/2016 0:00
      Nevada1045Home453/2/20035/30/2016 0:00
      Alabama1007Home74/22/19992/17/2016 0:00
      Alabama1042Home422/19/20015/27/2016 0:00
      Indiana1044Home443/1/20035/29/2016 0:00
      Kentucky1041Home412/18/20015/26/2016 0:00
      Colorado1046Home463/3/20035/31/2016 0:00
      Colorado1004Home42/19/20027/8/2016 0:00
      Ohio1043Home432/20/20013/11/2016 0:00
      Kentucky1006Home63/4/20036/1/2016 0:00
      Nevada1003Home32/4/20019/15/2016 0:00
      Nevada1010Home105/4/19992/24/2016 0:00
      Kentucky1013Home136/1/20161/24/2016 0:00
      Ohio1015Home158/4/20162/15/2016 0:00
      Alabama1014Home147/19/20161/17/2016 0:00
      South Carolina1012Home125/27/201610/31/2016 0:00
      Ohio1008Home84/26/200011/28/2016 0:00
      Indiana1016Home169/1/201611/28/2016 0:00
      Colorado1011Home115/10/200112/16/2016 0:00
      Indiana1009Home94/27/200112/28/2016 0:00
      Alabama1021Home211/2/2016 
      Nevada1178Home1781/3/2016 
      Colorado1018Home181/4/2016 
      South Carolina1054Home541/5/2016 
      Ohio1078Home782/15/2016 
      Nevada1080Home802/16/2016 
      South Carolina1019Home192/17/2016 
      Colorado1053Home532/18/2016 
      Kentucky1020Home202/19/2016 
      Colorado1025Home253/15/2016 
      Ohio1183Home1833/16/2016 
      Indiana1191Home1913/17/2016 
      Kentucky1027Home273/18/2016 
      South Carolina1026Home264/16/2016 
      Indiana1079Home794/17/2016 
      Colorado1081Home814/18/2016 
      Ohio1022Home224/19/2016 
      Nevada1192Home1925/11/2016 
      Nevada1017Home175/12/2016 
      Indiana1023Home235/13/2016 
      Nevada1024Home245/14/2016 
      Alabama1028Home285/15/2016 
      Ohio1029Home295/16/2016 
      Indiana1030Home306/18/2016 
      Nevada1031Home316/19/2016 
      Colorado1032Home326/20/2016 
      South Carolina1033Home336/21/2016 
      Kentucky1034Home346/22/2016 
      Alabama1035Home356/23/2016 
      Ohio1036Home367/3/2016 
      Indiana1037Home377/4/2016 
      Nevada1038Home387/5/2016 
      Colorado1039Home397/6/2016 
      South Carolina1040Home407/7/2016 
      South Carolina1047Home478/13/2016 
      Kentucky1048Home488/14/2016 
      Alabama1049Home498/15/2016 
      Ohio1050Home508/16/2016 
      Indiana1051Home519/18/2016 
      Nevada1052Home529/19/2016 
      Kentucky1055Home559/20/2016 
      Alabama1056Home569/21/2016 
      Ohio1057Home5710/2/2016 
      Indiana1058Home5810/3/2016 
      Nevada1059Home5910/4/2016 
      Colorado1060Home6011/19/2016 
      South Carolina1061Home6111/20/2016 
      Kentucky1062Home6211/21/2016 
      Alabama1063Home6311/22/2016 
      Ohio1064Home6412/14/2016 
      Indiana1065Home6512/15/2016 
      Nevada1066Home6612/16/2016 
      Colorado1067Home6712/17/2016 

       

       

      I want a count of homes licensed in a given month and year, and the count of homes closed in that same period.  I would like this information displayed in a clustered bar chart where I could filter by the year and it would display the count of homes licensed for Jan, Feb, Mar...on one axis.  On another axis the count of homes closed for the same time period. 

       

      4         2         5       3       7         7

      JAN   JAN    FEB   FEB   MAR    MAR

      LIC    CLO    LIC   CLO   LIC     CLO

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi hanlind,

         

        You can try to use below formulas:

         

        IsLiscen = 
var Liscen=MAX([LICENSED_DATE])
var Closen=MAX([CLOSED_DATE])
return
IF(Closen<>BLANK(),IF(MAX('Table'[Date])>=Liscen&&MAX('Table'[Date])<=Closen,1,0),IF(MAX('Table'[Date])>=Liscen,1,0))

         

        LiscenCount = SUMX(ALL(Sheet2),[IsLiscen])

         

        ClosedCount = COUNTX(FILTER(ALL(Sheet2),[CLOSED_DATE]<MAX('Table'[Date])&&[CLOSED_DATE]<>BLANK()),[HOMEID])+0

         

        Create a matrix visual, drag calendar table's year to Rows, month to Columns, then add "Liscencount" and "ClosedCount" measure to Values field.

         

        Regards,

        Xiaoxin Sheng 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi hanlind,

     

    You can refer to below steps to achieve your requirement:

     

    1. Create a CALENDAR table based on original table.

     

    Original table: home id, start date, end date.

     

    Table formula:

    Table = CALENDAR(MIN(Sheet1[StartDate]),MAX(Sheet1[EndDate]))

     

     

    2. Write formula to calculate licensed home count.

    licensedCount = CALCULATE(DISTINCTCOUNT(Sheet1[Homing ID]),FILTER(ALL(Sheet1),Sheet1[StartDate]<=MAX('Table'[Date])&&Sheet1[EndDate]>=MAX('Table'[Date])))

     

    3. Use the result of above measure to calculate the closed count.

     

    ClosedCount = CALCULATE(DISTINCTCOUNT(Sheet1[Homing ID])-[licensedCount],ALL(Sheet1))

     

    4. Create Clustered bar chart to show the result.

     

    Drag the date range of CALENDAR table to axis, switch to hierarchy mode and keep year and month fields. Drag measures to value fields.

     

     

    Regards,

    Xiaoxin Sheng