Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Hi @hanlind
Please share data and the output expected given that data. Put it on OneDrive or DropBox and share the link.
Cheers
CheenuSing
State | HOMEID | HOMENAME | LICENSED_DATE | CLOSED_DATE |
South Carolina | 1005 | Home5 | 2/17/1999 | 5/19/2016 0:00 |
Ohio | 1001 | Home1 | 1/25/1999 | 8/25/2016 0:00 |
Indiana | 1002 | Home2 | 1/28/2000 | 9/26/2016 0:00 |
Nevada | 1045 | Home45 | 3/2/2003 | 5/30/2016 0:00 |
Alabama | 1007 | Home7 | 4/22/1999 | 2/17/2016 0:00 |
Alabama | 1042 | Home42 | 2/19/2001 | 5/27/2016 0:00 |
Indiana | 1044 | Home44 | 3/1/2003 | 5/29/2016 0:00 |
Kentucky | 1041 | Home41 | 2/18/2001 | 5/26/2016 0:00 |
Colorado | 1046 | Home46 | 3/3/2003 | 5/31/2016 0:00 |
Colorado | 1004 | Home4 | 2/19/2002 | 7/8/2016 0:00 |
Ohio | 1043 | Home43 | 2/20/2001 | 3/11/2016 0:00 |
Kentucky | 1006 | Home6 | 3/4/2003 | 6/1/2016 0:00 |
Nevada | 1003 | Home3 | 2/4/2001 | 9/15/2016 0:00 |
Nevada | 1010 | Home10 | 5/4/1999 | 2/24/2016 0:00 |
Kentucky | 1013 | Home13 | 6/1/2016 | 1/24/2016 0:00 |
Ohio | 1015 | Home15 | 8/4/2016 | 2/15/2016 0:00 |
Alabama | 1014 | Home14 | 7/19/2016 | 1/17/2016 0:00 |
South Carolina | 1012 | Home12 | 5/27/2016 | 10/31/2016 0:00 |
Ohio | 1008 | Home8 | 4/26/2000 | 11/28/2016 0:00 |
Indiana | 1016 | Home16 | 9/1/2016 | 11/28/2016 0:00 |
Colorado | 1011 | Home11 | 5/10/2001 | 12/16/2016 0:00 |
Indiana | 1009 | Home9 | 4/27/2001 | 12/28/2016 0:00 |
Alabama | 1021 | Home21 | 1/2/2016 | |
Nevada | 1178 | Home178 | 1/3/2016 | |
Colorado | 1018 | Home18 | 1/4/2016 | |
South Carolina | 1054 | Home54 | 1/5/2016 | |
Ohio | 1078 | Home78 | 2/15/2016 | |
Nevada | 1080 | Home80 | 2/16/2016 | |
South Carolina | 1019 | Home19 | 2/17/2016 | |
Colorado | 1053 | Home53 | 2/18/2016 | |
Kentucky | 1020 | Home20 | 2/19/2016 | |
Colorado | 1025 | Home25 | 3/15/2016 | |
Ohio | 1183 | Home183 | 3/16/2016 | |
Indiana | 1191 | Home191 | 3/17/2016 | |
Kentucky | 1027 | Home27 | 3/18/2016 | |
South Carolina | 1026 | Home26 | 4/16/2016 | |
Indiana | 1079 | Home79 | 4/17/2016 | |
Colorado | 1081 | Home81 | 4/18/2016 | |
Ohio | 1022 | Home22 | 4/19/2016 | |
Nevada | 1192 | Home192 | 5/11/2016 | |
Nevada | 1017 | Home17 | 5/12/2016 | |
Indiana | 1023 | Home23 | 5/13/2016 | |
Nevada | 1024 | Home24 | 5/14/2016 | |
Alabama | 1028 | Home28 | 5/15/2016 | |
Ohio | 1029 | Home29 | 5/16/2016 | |
Indiana | 1030 | Home30 | 6/18/2016 | |
Nevada | 1031 | Home31 | 6/19/2016 | |
Colorado | 1032 | Home32 | 6/20/2016 | |
South Carolina | 1033 | Home33 | 6/21/2016 | |
Kentucky | 1034 | Home34 | 6/22/2016 | |
Alabama | 1035 | Home35 | 6/23/2016 | |
Ohio | 1036 | Home36 | 7/3/2016 | |
Indiana | 1037 | Home37 | 7/4/2016 | |
Nevada | 1038 | Home38 | 7/5/2016 | |
Colorado | 1039 | Home39 | 7/6/2016 | |
South Carolina | 1040 | Home40 | 7/7/2016 | |
South Carolina | 1047 | Home47 | 8/13/2016 | |
Kentucky | 1048 | Home48 | 8/14/2016 | |
Alabama | 1049 | Home49 | 8/15/2016 | |
Ohio | 1050 | Home50 | 8/16/2016 | |
Indiana | 1051 | Home51 | 9/18/2016 | |
Nevada | 1052 | Home52 | 9/19/2016 | |
Kentucky | 1055 | Home55 | 9/20/2016 | |
Alabama | 1056 | Home56 | 9/21/2016 | |
Ohio | 1057 | Home57 | 10/2/2016 | |
Indiana | 1058 | Home58 | 10/3/2016 | |
Nevada | 1059 | Home59 | 10/4/2016 | |
Colorado | 1060 | Home60 | 11/19/2016 | |
South Carolina | 1061 | Home61 | 11/20/2016 | |
Kentucky | 1062 | Home62 | 11/21/2016 | |
Alabama | 1063 | Home63 | 11/22/2016 | |
Ohio | 1064 | Home64 | 12/14/2016 | |
Indiana | 1065 | Home65 | 12/15/2016 | |
Nevada | 1066 | Home66 | 12/16/2016 | |
Colorado | 1067 | Home67 | 12/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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |