cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hanlind Frequent Visitor
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
Super User I
Super User I

Re: Tables with multiple date fields for a unique record.

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!
Community Support Team
Community Support Team

Re: Tables with multiple date fields for a unique record.

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 Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
hanlind Frequent Visitor
Frequent Visitor

Re: Tables with multiple date fields for a unique record.

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

Community Support Team
Community Support Team

Re: Tables with multiple date fields for a unique record.

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 Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors