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.
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.
Solved! Go to 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))
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!
@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))
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.
@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?
Yep, deidentified data attached.
https://www.dropbox.com/s/i18d6pjiy4wq3ob/Excel%20Reports%20-%20share.xlsx?dl=0
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 |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |