cancel
Showing results for
Did you mean:
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

## 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

## 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
Frequent Visitor

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

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

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

Hi @hanlind,

You can try to use below formulas:

```IsLiscen =
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

Announcements

#### 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?

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

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

Top Solution Authors
Top Kudoed Authors