(New to DAX from QlikView) - sum number of business days between Min and Max Date on Tx table

[TimMarsh]

Hi all, sorry for the seemingly simple Q. Checked the net first for calculate etc.

(Can't post the data as it's health data).

I have two tables:

One data/transactions (FACTS) table and a related calendar table, linked by a Date Field.

For each Tx Record, I add a Record Counter of 1.

I don't receive a transaction every day, I might have 7 one day, 0 the next, etc.

What I want to do is:

1. Count the number of business days and days (two variables) between my Min and Max date on the data table. (Again, new to DAX from QlikView, this would be easy in Qlik, and I am sure it will be easy here too).

2. I want to sum the number of record counter and divide by the number of business days to work out # of transactions per business day.

I've added an IsWorkDay DAX to my Calendar table:

=IF([Day Of Week Number]>0 && [Day Of Week Number]<6,1,0)

So I know if a Day of Week is a Work Day.

I have two measures on my Tx table for Max and Min Date:

MaxDate:=Max([Date2])

Same for MinDate.

What I need to do is sum the number of days between MinDate and MaxDate where the IsWorkDay flag in Calendar table is 1.

[harshnathani]

Hi @TimMarsh ,

You can have a look at these videos.

https://www.youtube.com/watch?v=kRACuS4eKWA

https://www.youtube.com/watch?v=JgeUhXkxXbU

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[amitchandak · Accepted solution]

@TimMarsh , Ideally sum(IsWorkDay)  should work as slicer is coming from this table.

or you can try

measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return calculate(sum(IsWorkDay), filter(Allselected(Date),Date[Date]>=_min && Date[Date]<=_max))

[TimMarsh]

Hi @amitchandak, and thanks! (Be gentle, am very new to this!)

I can't use a slicer (I think) as I actually want to calculate to use as a measure.

Here's what I used as the measure, but I get a result of 1. Is there a way to display the variables to see what value they're storing?

I wonder if it's because Date2 is a timetstamp eg 7/06/2020 12:00:00 AM

** EDIT, my min function was maxx. now working!

NUM WORK DAYS:=
var 
	_max = maxx(ALLSELECTED('Table1 1'),'Table1 1'[Date2])
var 	
	_min = maxx(ALLSELECTED('Table1 1'),'Table1 1'[Date2])
RETURN 
calculate(sum('Calendar'[IsWorkDay]), filter(Allselected('Calendar'),'Calendar'[Date]>=_min && 'Calendar'[Date]<=_max))

Calendar is my calendar table, Table 1 1 is my FACTS table and Date2 is my datefield in table 1, and linked to Date in Calendar.

[amitchandak]

@TimMarsh , what are dates selected in slicer. Also when you joined a date with Table date slicer should be on Date table.

Can you share sample data and sample output in table format?

[TimMarsh]

@amitchandak 

Yep, deidentified data attached. 

https://www.dropbox.com/s/i18d6pjiy4wq3ob/Excel%20Reports%20-%20share.xlsx?dl=0