Measure to Count number of dates in each month

[Mavericks334]

Hi,

I have 5 tables, Period(Month) Table, Date Table, Year, Year Period Table and Raw Data table. There is a relationship between each of these table.

From the raw data table i have two columns Actual Date Out and Actual date back.

How do i create metric to get the count for the number of days for Actual date out and actual date back for each month. The month Name is the column.

Regards,
Renato.

 

[saeedkk]

Hello Renato,

can you provide a sample so it would be clear please

regards
saeed khodor

 

[Mavericks334]

Hi,

Below are the sample tables,

Table 1 Ops Team
Item Id Status Expected Due Out Actual Due Out Expected Due Back Actual Due Back
49308 Closed 9/1/2015 9/8/2015 9/2/2015 9/8/2015
52420 Closed 9/2/2015 9/2/2015 9/3/2015
52421 Closed 9/3/2015 9/3/2015 9/3/2015 9/3/2015
52425 Closed 9/9/2015 9/9/2015 9/9/2015 9/9/2015
52430 Closed 9/14/2015 9/14/2015 9/14/2015
52431 Closed 9/15/2015 9/15/2015 9/15/2015 9/21/2015

Table 2 Audit Team
Item Id Status Date Pending Audit Audit by Date Close Date
53566 Closed 8/19/2015 8/20/2015 8/19/2015
53702 Closed 9/1/2015 9/2/2015 9/1/2015
53703 Closed 9/1/2015 9/2/2015 9/1/2015
53696 Closed 9/18/2015 9/2/2015 9/18/2015
53707 Closed 9/1/2015 9/2/2015 9/1/2015
53708 Closed 9/8/2015 9/8/2015

Below is how i would like to have the summary with the year as filter.

Status July August September October NovemberActual Out
Actual In
Audit Closed



The date table is linked to both these tables.

Regards,
Renato.

 

[scottsen]

Depends on how you intend to use this...

NumDays := COUNTROWS(Calendar)

... typically works out just fine for me.

 

[Mavericks334]

Thanks Scott. This would only give me the count of rows in the calendar table. I need the count from the other two tables and show it in a single summary pivot.

 

[scottsen]

Somehow I read " get the count for the number of days for " and went there.

So, I'm thinking you kinda want DataBack - DataOut = NumDays? Like, 2015-08-05 -> 2015-08-25 is 20 (or 21?) days. And you can literally just subtract 2 dates to get a # of days.

However, I suspect you are asking for something a bit more complicated, like 2015-08-05 -> 2015-10-25 means you were "out" for all the days in Sept (plus some in Aug/Oct).

For that, I recommend you search the googles... for "DAX Events in Progress", as I know there are some good blogs on that pattern.