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.
I have the following example table:
id | opening date | closing date |
1 | 01/01/2021 | 01/02/2021 |
2 | 01/01/2021 | 01/02/2021 |
3 | 01/01/2021 | 01/02/2021 |
4 | 01/01/2021 | 01/02/2021 |
5 | 01/01/2021 | |
6 | 01/01/2021 | |
7 | 01/01/2021 | |
8 | 01/01/2021 | |
9 | 01/02/2021 | |
10 | 01/02/2021 | |
11 | 01/02/2021 | |
12 | 01/02/2021 | |
13 | 01/02/2021 | |
14 | 01/02/2021 | |
15 | 01/02/2021 | |
16 | 01/02/2021 | |
17 | 01/03/2021 | |
18 | 01/03/2021 | |
19 | 01/03/2021 | |
20 | 01/03/2021 |
Each row of the table is a task (demand) that has been generated for someone. The objective is to create a histogram that shows the amount of accumulated open demands. An open demand is one that does not have an closing date, or whose closing date is later than the month bar in the histogram. The result should be something like:
*i know this is not a histogram
Note that 8 demands were generated in January, 8 in February and 4 in March, but 4 were closed in February.
Could someone help me create this chart?
Solved! Go to Solution.
@FranciscoHoff you can create a measure like this
Measure =
VAR _filter =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] <= MAX ( 'Calendar'[Year] )
&& 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
)
VAR _opening =
CALCULATE (
COUNT ( demand[opening date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
)
VAR _closing =
CALCULATE (
COUNT ( demand[closing date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
)
VAR _accumulatedOpening =
CALCULATE (
CALCULATE (
COUNT ( demand[opening date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
),
_filter,
demand
)
VAR _accumulatedClosing =
CALCULATE (
CALCULATE (
COUNT ( demand[closing date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
),
_filter,
demand
)
VAR _diff = _accumulatedOpening - _accumulatedClosing
RETURN
IF ( _opening <> BLANK () || _closing <> BLANK (), _diff )
@FranciscoHoff you can create a measure like this
Measure =
VAR _filter =
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Year] <= MAX ( 'Calendar'[Year] )
&& 'Calendar'[Month] <= MAX ( 'Calendar'[Month] )
)
VAR _opening =
CALCULATE (
COUNT ( demand[opening date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
)
VAR _closing =
CALCULATE (
COUNT ( demand[closing date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
)
VAR _accumulatedOpening =
CALCULATE (
CALCULATE (
COUNT ( demand[opening date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] )
),
_filter,
demand
)
VAR _accumulatedClosing =
CALCULATE (
CALCULATE (
COUNT ( demand[closing date] ),
TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] )
),
_filter,
demand
)
VAR _diff = _accumulatedOpening - _accumulatedClosing
RETURN
IF ( _opening <> BLANK () || _closing <> BLANK (), _diff )
I know this is not a question directly related to the post, but could you help me understand some details of your code? I'm new to the platform and haven't had much contact with DAX.
1) In the FILTER function of the _filter variable, how does the second part of the filter parameter after "&&" not return a month greater than a month from another year that is not the current context year? For example, if it has already processed all the months of 2020, how does it not return the month of December for all entries for the year 2021?
2) Could the CALCULATE nested in the _accumulatedOpening and _accumulatedClosing variable be replaced by the _opening and _closing variables?
3) Why is the IF clause at the end necessary?
Again, thanks for your help.
worked perfectly! The only problem I had was that my original data was in datetime, so I needed to convert it to date.
Thanks!!!
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |