CODEWITHSUNDEEP
powerbidax
Kalenji
Kalenji

Reputation: 407

Power BI - count active items between two dates

I have a query I tried to resolve but I failed badly.

I have a table with items and want to count active ones per day. I did it in excel where I created another column with the last 7 days and I used countifs.

So my excel formula is 

=COUNTIFS($A$3:$A$30001,"<="&F3,$B$3:$B$30001,">="&F3)

But I need the same in Power BI.

My table with data is called ADW_DEFECTS and has two columns with open and closed dates.

ISSUE_DTTM  CLOSE_DTTM
26/11/2019  
26/11/2019  
26/11/2019  
26/11/2019  
26/11/2019  
25/11/2019  
25/11/2019  
25/11/2019  
24/11/2019  25/11/2019
24/11/2019  25/11/2019
24/11/2019  25/11/2019
24/11/2019  26/11/2019
23/11/2019  24/11/2019
23/11/2019  24/11/2019
23/11/2019  25/11/2019
22/11/2019  22/11/2019

Now I figure out I need to pivot to another table for the last seven days' calculations (I used a table called NEW.DEFECTS_ACTIVE).

This is the result of excel

Dates       Active
26/11/2019  1
25/11/2019  5
24/11/2019  7
23/11/2019  3
22/11/2019  1
21/11/2019  0
20/11/2019  0

So, the required result in Power BI is a table with the same as excel one.

Upvotes: 3

Views: 16060

Answers (2)

OscarLar
OscarLar

Reputation: 1335

You can also expand the table like this, though you end up with a second table:

Expanded_ADW = 
GENERATE(
    CALCULATETABLE(
        ADW_DEFECTS;
        ADW_DEFECTS[CLOSE_DTTM] <> BLANK()
    );
    SELECTCOLUMNS(
        GENERATESERIES(
            ADW_DEFECTS[ISSUE_DTTM];
            ADW_DEFECTS[CLOSE_DTTM]
        );
        "Expanded_Date"; [Value]
    )
)

Then have a calendar with a 1:* relationship with [Expanded_Date]. In a visual table with date from the Calendar tabel add this measure:

Active = COUNTROWS('Expanded_ADW')+0

Then you have a table like this:

enter image description here

Upvotes: 4

Mike Honey
Mike Honey

Reputation: 15037

First I would create a Dates table. There are many ways to do this - for more complex requirements I prefer Power Query but for a simple demo you can go to the Modeling ribbon, choose New table and enter:

Dates = CALENDARAUTO(1)

Next review the Model view and make sure there are no relationships between the new Dates table and your existing ADW_DEFECTS table.

Then I would go to the Modeling ribbon and choose New measure, and copy in this DAX formula:

Active =
SUMX (
    Dates,
    CALCULATE (
        COUNTROWS ( ADW_DEFECTS ),
        FILTER (
            ADW_DEFECTS,
            [Date] >= ADW_DEFECTS[ISSUE_DTTM]
                && [Date] <= ADW_DEFECTS[CLOSE_DTTM]
        )
    )
)

This basically says for each row in Dates, count how many rows from ADW_DEFECTS are "Active". You have to imagine the Measure formula running in every cell of your output visual. The outer SUMX will calculate a total for all Dates by summing the Date-level results - e.g. how many "Days Active".

Finally add a Table visual to the Report view. Add the Date column from the Dates table and the Active measure. Turn off the Totals if you don't want to show that.

It doesn't produce the extra rows with zeros, e.g. 20/11/2019, but they seem arbitrary. If they are actually required, just add + 0 to the measure formula and filter the Date column on the Table visual.

Upvotes: 2

Related Questions