Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hanlind
Frequent Visitor

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.

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
CheenuSing
Community Champion
Community Champion

Hi @hanlind

 

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

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

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 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.