Multiple Date Fields

[Anonymous]

I have a table that looks like this:

Using a single date slicer, I would like to see the amount of companies created and the amount of companies closed within a selected date range. The goal is to put it on a line graph to compare. Right now I can only establish a relationship to a date table to one of these fields, so I don't know how to get around that:

The number 922 (close date field) is correct because that is where my relationship is established to my date table, but I know the 2342 number is incorrect (created date field). It is supposed to be 2564 for the date range I'm looking at. How can I correct this? These are cumulative totals by the way.

[v-yuta-msft]

@Anonymous ,

You may create two measures using dax below:

Companies Created =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER ( ALL ( Table ), Table[CreatedDate] IN VALUES ( Date[Date] ) )
)

Companies Created =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER ( ALL ( Table ), Table[CloseDate] IN VALUES ( Date[Date] ) )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft  and @JasonTX , these solutions did not give me quite what I was looking for. I will break it down a little further. So I have a relationship between my main table and my date table (CloseDate->Date). Represented by the colors below. I have an inactive relationship setup between CreatedDate->Date. So if I set the Date or CloseDate slicer to 2019, I get the correct number for companies with a 2019 CloseDate.

Now lets say I want to look at the number of companies with a 2019 Created Date using just my CreatedDate slicer:

I know the number should be 2564. Now if I reset all slicers and use just my date slicer from my date table to look at 2019, the number for Created Date changes:

So I know 2348 is incorrect. Also note the Created Date column above shows companies with 2018 dates. I know this is happening due to my relationship between Close Date->Date, and was wondering if there was some kind of way around this? I tried both of your solutions but it returns the same numbers I already had. I'm not sure if it is possible to pull off what I'm doing. I need to maintain the active relationship between CloseDate->Date for another formula I'm using.

[JasonTX]

If you just want a measure count, this can help:

https://stackoverflow.com/questions/55873643/filter-data-between-start-date-and-end-date-in-power-bi

However, if you would like to create a table as mentioned, you will have to create a new columns and several measures mentioned below:

Column:

Namesfilter = Calculate(VALUES([Name]))

Measures:

CreatedDateFiltered = 
var a = FILTER(Table1, Table1[CreatedDate] = MIN('Date Table'[Date]) && Table1[CloseDate] = MAX('Date Table'[Date]))
return MINX(a, Table1[CreatedDate])

ClosedDateFiltered = 
var a = FILTER(Table1, Table1[CreatedDate] = MIN('Date Table'[Date]) && Table1[CloseDate] = MAX('Date Table'[Date]))
return MAXX(a, Table1[CloseDate])

CountFilter = COUNTROWS(FactInternetSales)

Make sure CreatedDateFiltered and ClosedDateFiltered are filtered to 'is not blank'.

Try this and see if it is closer to what you are looking for.

[Anonymous · Accepted solution]

Thank you @JasonTX ! I did figure out a way to get it by searching through another thread. Highlighting both date columns in the query editor, then unpivoting those columns did the trick. Then I have a single date column from my main table connected to my date table, and an additional column that allowed me to toggle between Close Date and Created Date. This is allowed me to write the following measures:

And this ended up giving me what I was looking for. I appreciate all your help! You as well @v-yuta-msft !

[JasonTX]

Draw an inactive relationship between CreatedDate and your date in the date table. 

Revaluate your measure by using something like:

Calculate(<expression>, USERELATIONSHIP('table'[CreatedDate], 'Datetable'[Date]))

Please let me know if this works!