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.
Hello everybody !
We have a list of people with a start date & end date.
I want to know how many people were active over time (meaning : start date < beginning of a period & end date > end of a period). The period being every month.
The result expected :
Jan 2018 - 18
Feb 2018 - 17
Mar 2018 - 18
Apr 2018 - 19
etc...
Not so obvious, I don't see how to create a measure for that. should I use a date table and an intermediate table to count for each different day ? it seems quite complicated, maybe there is an easier solution ? Thanks in advance !
Solved! Go to Solution.
Hi @François,
i would think in your scenario you could use a disconnected date table as you would always be removing the filter context on the start date where you would likely be joined.
I following will work in that scenario
Measure = Calculate(countrows('table'),
filter('table', 'table'[startdate]< min('date'[date]) && 'table'[enddate] > max('date'[date]))
If you are able to provide a sample file or data, I could give you a more exact formula
Proud to be a Super User!
hi, @François
You could try to refer to this post:
Best Regards,
Lin
Hi @François,
i would think in your scenario you could use a disconnected date table as you would always be removing the filter context on the start date where you would likely be joined.
I following will work in that scenario
Measure = Calculate(countrows('table'),
filter('table', 'table'[startdate]< min('date'[date]) && 'table'[enddate] > max('date'[date]))
If you are able to provide a sample file or data, I could give you a more exact formula
Proud to be a Super User!
Below are measures I use for this type of thing. The first two are what you're asking for, and I included some additional helpful ones since you may be doing something similar.
Note that these measures require the calDate to be used in the visual or as a filter because there is no relationship between the calendar and customer tables.
customerQty = COUNTROWS ( customers ) qtyActiveBeginningOfPeriod = CALCULATE ( customerQty , FILTER ( customers , startDate < min (calDate) && endDate > min (calDate)) ) qtyActiveEndOfPeriod = CALCULATE ( customerQty , FILTER (customers , endDate > max (calDate)) ) qtyRetainedBase = CALCULATE ( qtyActiveEndOfPeriod , FILTER ( customers , startDate < min (calDate)) ) qtyGrossNew = CALCULATE ( customerQty , FILTER ( customers , startDate >= min (calDate) && startDate <= max (calDate)) ) qtyNetNew = CALCULATE ( qtyGrossNew , FILTER ( customers , endDate > max (calDate)) )
Usually after these measures, I build a summary table with SUMMARIZECOLUMNS by Year-Month, and I will relate that table to a calendar. Most of my visuals will be build on the summary table and another set of measures created against it.
Hi
Trying hard to get this to work.
Can you please show the DAX to build the summary table?
Regards, Henrik
Hi,
Share some data and show the expected result.
House1, Startdate = 01-01-2010, Enddate = 01-01-2020
House2, Startdate = 01-01-2010, Enddate = 01-01-2020
House3, Startdate = 01-01-1991, Enddate = 01-01-2007
Person 1, Moved out date = 01-05-2018
Person 2, Moved out date = 01-05-2019
Active houses
2017 2018 2019
2 2 2
Number of persons moved from houses
2017 2018 2019
0 1 1
Number of persons moved from houses as pct. of active houses, year-by-year
2017 2018 2019
0 50 50
Hi,
I do not know how to compute Active houses - why does a figure of 2 appear for 2017? You may download my PBI file from here.
Hope this helps.
Thansk - but the essense of this thread is the calution of Active houses,using a start- and enddate.
I understand how to do that with the description from François.
My problem is the last calulation "Number of persons moved from houses as pct. of active houses, year-by-year"
I think I could figure it out, if I understood the last part of the message from @François: "Usually after these measures, I build a summary table with SUMMARIZECOLUMNS by Year-Month, and I will relate that table to a calendar. Most of my visuals will be build on the summary table and another set of measures created against it" ... but I cant, alas.
Here it my case again:
HOUSES
Number Startdate Enddate
1 01-01-2010 01-01-2020
2 01-01-2010 01-01-2020
3 01-01-1991 01-01-2007
PERSONS
Number MouvedOut
1 01-05-2018
2 01-05-2019
EXPECTED RESULT
Active houses:
2017 2018 2019
2 2 2
Number of persons moved from houses:
2017 2018 2019
0 1 1
Number of persons moved from houses as pct. of active houses, year-by-year
2017 2018 2019
0 50 50
my guess is COUNTROWS with FILTER on your table with starts/ends dates - can you share how it looks?
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |