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.
I have a table with service jobs. The fields is "Title", "Start date", "Stop date".
eg.
Title Start date Stop date
"Service on production machine A. 01.01.2020 01.03.2020
The production machine A is not available in the service period (between the start and stop date). I have a lot of service jobs, like this.
I like to count the number of service jobs in the period, eg. Januar, February, March, April or based on week number, and for the example this service job should be included in the number of service jobs, for the whole time periode. If sum on a month, i like this job to be counted in both january, february and march.
How should the DAX expression look like to count this for every time period between the dates. Any idea ?
Solved! Go to Solution.
Hi @TrulsB
Create a date table ( this date table has no relationship with your table)
DATE =
ADDCOLUMNS (
CALENDARAUTO (),
"year", YEAR ( [Date] ),
"quarter", SWITCH (
TRUE,
MONTH ( [Date] )
IN {
1,
2,
3
}, "Q1",
MONTH ( [Date] )
IN {
4,
5,
6
}, "Q2",
MONTH ( [Date] )
IN {
7,
8,
9
}, "Q3",
MONTH ( [Date] )
IN {
10,
11,
12
}, "Q4"
),
"monthname", FORMAT (
[Date],
"mmm"
),
"monthno", FORMAT (
[Date],
"yyyy-mm"
),
"week", WEEKNUM (
[Date],
2
)
)
Create calcualted columns in this date table
start of month = STARTOFMONTH('DATE'[Date])
end of month = ENDOFMONTH('DATE'[Date])
start of quarter = STARTOFQUARTER('DATE'[Date])
end of quarter = ENDOFQUARTER('DATE'[Date])
start of year = STARTOFYEAR('DATE'[Date])
end of year = ENDOFYEAR('DATE'[Date])
Add columns from Date table to the X-axis of the visual as below
Then create measures
day level =
CALCULATE (
DISTINCTCOUNT ( 'Table'[machine] ),
FILTER (
'Table',
'Table'[Start date]
<= MAX ( 'DATE'[Date] )
&& 'Table'[Stop date]
>= MAX ( 'DATE'[Date] )
)
)
month level =
CALCULATE (
DISTINCTCOUNT ( 'Table'[machine] ),
FILTER (
'Table',
'Table'[Stop date]
>= MAX ( 'DATE'[start of month] )
&& 'Table'[Start date]
<= MAX ( 'DATE'[end of month] )
)
)
quarter level =
CALCULATE (
DISTINCTCOUNT ( 'Table'[machine] ),
FILTER (
'Table',
'Table'[Stop date]
>= MAX ( 'DATE'[start of quarter] )
&& 'Table'[Start date]
<= MAX ( 'DATE'[end of quarter] )
)
)
year level =
CALCULATE (
DISTINCTCOUNT ( 'Table'[machine] ),
FILTER (
'Table',
'Table'[Stop date]
>= MAX ( 'DATE'[start of year] )
&& 'Table'[Start date]
<= MAX ( 'DATE'[end of year] )
)
)
Final result =
IF (
ISINSCOPE ( 'DATE'[Date] ),
[day level],
IF (
ISINSCOPE ( 'DATE'[week] ),
[day level],
IF (
ISINSCOPE ( 'DATE'[monthname] ),
[month level],
IF (
ISINSCOPE ( 'DATE'[quarter] ),
[quarter level],
[year level]
)
)
)
)
Hi @TrulsB
Create a date table ( this date table has no relationship with your table)
DATE =
ADDCOLUMNS (
CALENDARAUTO (),
"year", YEAR ( [Date] ),
"quarter", SWITCH (
TRUE,
MONTH ( [Date] )
IN {
1,
2,
3
}, "Q1",
MONTH ( [Date] )
IN {
4,
5,
6
}, "Q2",
MONTH ( [Date] )
IN {
7,
8,
9
}, "Q3",
MONTH ( [Date] )
IN {
10,
11,
12
}, "Q4"
),
"monthname", FORMAT (
[Date],
"mmm"
),
"monthno", FORMAT (
[Date],
"yyyy-mm"
),
"week", WEEKNUM (
[Date],
2
)
)
Create calcualted columns in this date table
start of month = STARTOFMONTH('DATE'[Date])
end of month = ENDOFMONTH('DATE'[Date])
start of quarter = STARTOFQUARTER('DATE'[Date])
end of quarter = ENDOFQUARTER('DATE'[Date])
start of year = STARTOFYEAR('DATE'[Date])
end of year = ENDOFYEAR('DATE'[Date])
Add columns from Date table to the X-axis of the visual as below
Then create measures
day level =
CALCULATE (
DISTINCTCOUNT ( 'Table'[machine] ),
FILTER (
'Table',
'Table'[Start date]
<= MAX ( 'DATE'[Date] )
&& 'Table'[Stop date]
>= MAX ( 'DATE'[Date] )
)
)
month level =
CALCULATE (
DISTINCTCOUNT ( 'Table'[machine] ),
FILTER (
'Table',
'Table'[Stop date]
>= MAX ( 'DATE'[start of month] )
&& 'Table'[Start date]
<= MAX ( 'DATE'[end of month] )
)
)
quarter level =
CALCULATE (
DISTINCTCOUNT ( 'Table'[machine] ),
FILTER (
'Table',
'Table'[Stop date]
>= MAX ( 'DATE'[start of quarter] )
&& 'Table'[Start date]
<= MAX ( 'DATE'[end of quarter] )
)
)
year level =
CALCULATE (
DISTINCTCOUNT ( 'Table'[machine] ),
FILTER (
'Table',
'Table'[Stop date]
>= MAX ( 'DATE'[start of year] )
&& 'Table'[Start date]
<= MAX ( 'DATE'[end of year] )
)
)
Final result =
IF (
ISINSCOPE ( 'DATE'[Date] ),
[day level],
IF (
ISINSCOPE ( 'DATE'[week] ),
[day level],
IF (
ISINSCOPE ( 'DATE'[monthname] ),
[month level],
IF (
ISINSCOPE ( 'DATE'[quarter] ),
[quarter level],
[year level]
)
)
)
)
Hi @TrulsB
Do you have tables like this?
Title | Start date | Stop date |
"Service on production machine A. | 1/1/2020 | 1/3/2020 |
"Service on production machine B. | 1/10/2020 | 3/28/2020 |
You want to calculate counts for machine A,B,C..,ect in date periods (year,month,week,,ect), right?
i have a doubt that you say "The production machine A is not available in the service period (between the start and stop date)",
is this mean the counts should not include the machine A?
But "for the example this service job should be included in the number of service jobs, for the whole time periode",
I don't understand well about your statement and requirements.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
eg.
If i create a graph for every day from 1st january to 38th march, the Machine A should be in the graph for 1st, 2nt and 3rd january, and Machine B should be in the graph for every day from 10 january to 28 march.
Refer, if this can help
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |