CODEWITHSUNDEEP
powerbipowerbi-desktop
Vitor
Vitor

Reputation: 1

How do I plot a line graph that keeps counting a row in the intermediary months between entry and leave date?

I have a table ISSUE with these columns:

ID
ENTRY_DATE
LEAVE_DATE

I would like to plot a countrows in a line graph all the period it stayed open.

The line graph will have date in X axis, and a count of logs that was open in that period in Y axis.

E.g one issue log have an entry of January and it leaves on July, in the graph I want to this log to be counted in January, February, March... until July.

Example of what I´m trying to make

Already have a date dimension table starting in minimun ENTRY_DATE till TODAY.

I would like to keep counting for each month it is open.

I have tried with this:

COUNT_ROWS = 
    CALCULATE( 
        COUNTROWS(ISSUE)
        ,ISSUE[ENTRY_DATE] <= MAX(ISSUE[Date])
        ,ISSUE[LEAVE_DATE] >= MIN(ISSUE[Date])
    )

Upvotes: 0

Views: 25

Answers (1)

Ryan
Ryan

Reputation: 2482

This is the sample data I used for test.

enter image description here

create a date table and do not create relationships between two tables

enter image description here

at last, create a measure

MEASURE =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Entry] <= MAX ( 'date'[Date] )
            && 'Table'[Leave] >= MIN ( 'date'[Date] )
    )
)

enter image description here

Upvotes: 0

Related Questions