Hi Community, I would like to get some inspiration/help to create a DAX measure. For my report, I need to display the count of statusses over time, using a start and enddate. Ideally, this should be dynamic. So that it works with both days, weeks and months on the axes. This is what the data looks like (text and picture format): statusstartDateendDateXXX1-1-202429-2-2024YYY1-2-202415-2-2024BBB1-1-202430-4-2024XXX1-1-202431-3-2024 And this is how it should be visualized (example with months, but should work with days & weeks too): I have difficulty with creating something like this, so it would be great if some of you could help out! If you have any idea, please provide the DAX! Kind regards

Anonymous If I understand your problem correctly, you want to count the number of ongoing statuses between a given start date and end date.For example:For status XXX, one started on 1st Jan 2024 and ended on 29th Feb 2024, and another started on 1st Jan 2024 and ended on 31st March 2024.In January, the count should be 2 since both started in this month.In February, the count should still be 2 because the first one ends in February, and the second one, which ends in March, is still ongoing.In March, the count would be 1 as only the second status is ongoing.For status BBB, starting in January 2024 and ending in April, the count for February, March, and April will be 1 each month, as it continues through all those months.The same logic applies to weekly calculations.If this understanding is correct, here’s my suggested solution:Prepare your data: Go to Power Query and generate a list of rows for each status between the start and end dates.Create a Date table: You can create this in either Power Query or using DAX.Write a small DAX.The complete solution is in the link below.https://we.tl/t-t7VUBbwnerbelow screenshot While I could solve this using DAX, but I always follow Roche's Maxim rule "Data should be transformed as far upstream as possible, and as far downstream as necessary."If you need a DAX-only solution, please allow me some time to provide it. I must mention that it’s not an easy task. Hope this helps!RegardssanalyticsIf it is your solution then please like and accept it as solution

Forum Discussion

Anonymous

Not applicable

1 year ago

Solved

Counting objects over time with startDate & endDate

Hi Community,

I would like to get some inspiration/help to create a DAX measure. For my report, I need to display the count of statusses over time, using a start and enddate. Ideally, this should be dynamic. So that it works with both days, weeks and months on the axes.

This is what the data looks like (text and picture format):

status	startDate	endDate
XXX	1-1-2024	29-2-2024
YYY	1-2-2024	15-2-2024
BBB	1-1-2024	30-4-2024
XXX	1-1-2024	31-3-2024

And this is how it should be visualized (example with months, but should work with days & weeks too):

I have difficulty with creating something like this, so it would be great if some of you could help out! If you have any idea, please provide the DAX!

Kind regards

sanalytics
1 year ago
Anonymous
If I understand your problem correctly, you want to count the number of ongoing statuses between a given start date and end date.
For example:
For status XXX, one started on 1st Jan 2024 and ended on 29th Feb 2024, and another started on 1st Jan 2024 and ended on 31st March 2024.
In January, the count should be 2 since both started in this month.
In February, the count should still be 2 because the first one ends in February, and the second one, which ends in March, is still ongoing.
In March, the count would be 1 as only the second status is ongoing.
For status BBB, starting in January 2024 and ending in April, the count for February, March, and April will be 1 each month, as it continues through all those months.
The same logic applies to weekly calculations.
If this understanding is correct, here’s my suggested solution:
Prepare your data: Go to Power Query and generate a list of rows for each status between the start and end dates.
Create a Date table: You can create this in either Power Query or using DAX.
Write a small DAX.
The complete solution is in the link below.
https://we.tl/t-t7VUBbwner
below screenshot

While I could solve this using DAX, but I always follow Roche's Maxim rule "Data should be transformed as far upstream as possible, and as far downstream as necessary."
If you need a DAX-only solution, please allow me some time to provide it. I must mention that it’s not an easy task.

Hope this helps!
Regards
sanalytics
If it is your solution then please like and accept it as solution

Anonymous

1 year ago

Hi Anonymous

Thank you very much sanalytics、Selva-Salimi and bhanu_gautam for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

"StatusTable"

Create a Date Table.

DateTable = 
ADDCOLUMNS (
    CALENDAR (MIN('StatusTable'[startDate]), MAX('StatusTable'[endDate])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Week", WEEKNUM([Date]),
    "Day", DAY([Date])
)

Create a mesure.

StatusCount = 
VAR max_d = MAX('DateTable'[Date])
var min_d = MIN('DateTable'[Date])
VAR CountStatuses = 
    CALCULATE (
        COUNTROWS('StatusTable'),
        FILTER (
            'StatusTable',
            'StatusTable'[startDate] <= max_d &&
            'StatusTable'[endDate] >= min_d
        )
    )
RETURN
IF(ISBLANK(CountStatuses), 0, CountStatuses)

Create a Line chart visual.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

status count.pbix68 KB

5 Replies

sanalytics
Super User
1 year ago
Anonymous
If I understand your problem correctly, you want to count the number of ongoing statuses between a given start date and end date.
For example:
For status XXX, one started on 1st Jan 2024 and ended on 29th Feb 2024, and another started on 1st Jan 2024 and ended on 31st March 2024.
In January, the count should be 2 since both started in this month.
In February, the count should still be 2 because the first one ends in February, and the second one, which ends in March, is still ongoing.
In March, the count would be 1 as only the second status is ongoing.
For status BBB, starting in January 2024 and ending in April, the count for February, March, and April will be 1 each month, as it continues through all those months.
The same logic applies to weekly calculations.
If this understanding is correct, here’s my suggested solution:
Prepare your data: Go to Power Query and generate a list of rows for each status between the start and end dates.
Create a Date table: You can create this in either Power Query or using DAX.
Write a small DAX.
The complete solution is in the link below.
https://we.tl/t-t7VUBbwner
below screenshot

While I could solve this using DAX, but I always follow Roche's Maxim rule "Data should be transformed as far upstream as possible, and as far downstream as necessary."
If you need a DAX-only solution, please allow me some time to provide it. I must mention that it’s not an easy task.

Hope this helps!
Regards
sanalytics
If it is your solution then please like and accept it as solution
bhanu_gautam
Super User
1 year ago
Anonymous ,Create a Date table if you don't already have one. This table should include all the dates you want to analyze.

Create a measure to count the statuses over time

Status Count =
VAR SelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[startDate] <= SelectedDate &&
'YourTable'[endDate] >= SelectedDate
)
)
- Anonymous
  Not applicable
  1 year ago
  Unfortunately I can't get it to work. These are the results I get:
  
  I also wondered if I shouldn't do something with the relationship between the endDate and Date from my datetable? Right now I only have a relationship between startDate and Date:
  I suppose the measure also needs to be adjusted when I create two relationships.
Selva-Salimi
Solution Sage
1 year ago
Hi Anonymous

you can write a measure as follows:
CountStatusesOverTime =
VAR SelectedDate = SELECTEDVALUE('DimDate '[Dates])
var statusCount = CALCULATE(COUNT(Tbl[Status]) , FILTER((Tbl) , Tbl[StartDate]<= SelectedDate && Tbl[EndDate] >= SelectedDate) )
RETURN
statusCount+0

** you should not have relation between dimdate and tbl and use dates of DimDate in X-axis

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Anonymous

Not applicable

1 year ago

Hi Anonymous

Thank you very much sanalytics、Selva-Salimi and bhanu_gautam for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

"StatusTable"

Create a Date Table.

DateTable = 
ADDCOLUMNS (
    CALENDAR (MIN('StatusTable'[startDate]), MAX('StatusTable'[endDate])),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Week", WEEKNUM([Date]),
    "Day", DAY([Date])
)

Create a mesure.

StatusCount = 
VAR max_d = MAX('DateTable'[Date])
var min_d = MIN('DateTable'[Date])
VAR CountStatuses = 
    CALCULATE (
        COUNTROWS('StatusTable'),
        FILTER (
            'StatusTable',
            'StatusTable'[startDate] <= max_d &&
            'StatusTable'[endDate] >= min_d
        )
    )
RETURN
IF(ISBLANK(CountStatuses), 0, CountStatuses)

Create a Line chart visual.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

status count.pbix68 KB

Forum Discussion

Counting objects over time with startDate & endDate

5 Replies

Related content

Counting nulls

Count Of Drivers

Filters issues in visual objects

Error - Object reference not set to an instance of an object

Count for every day from startdate and enddate