Show data in every month when active, not all has start and end date

[paprika96]

Hello! I hope you can help me. 

I have this huge dataset with bids. I need to show visualize their active months. 

So they need to 'count as 1' in all the months that are active. If they do not have a start date, they should not be included and if they do not have a stop date they should be in the chart from their start date and until they get an end date. 

Each bid has their own unique ID.

I hope this makes sense and that you can help me. As I can only get them to show the month of their start date and not the months inbetween. 

The start date is the: bid_short_date

And the end date is the: Date_won_lost

[v-yanjiang-msft]

Hi @paprika96 ,

Sorry I'm not very clear about your expected result. According to your description, for each bid, there're start date and end date, so you want to count the bids which meet the condition you mentioned in the columns or some else, would you please explain details about it?

Best Regards,
Community Support Team _ kalyj

[paprika96]

I am sorry for net being clear, new to power bi 🙂 
I need a chart like below, but showing the bids in their active months. In the one below it only shows the bids which has no end date and only in the month of the start date. 

So, i want it to show the bids in the chart in the months they are active. 

So if i have a start date 01.01.2022 and enddate 01.04.2022 it needs to count as one in the chart in all four months. If they have no enddate they should be visual from the startdate to today. 

The bids with no start date at all should not be in this chart. 

 I hope this make a bit more sense? 🙂 

[v-yanjiang-msft · Accepted solution]

Hi @paprika96 ,

According to your description, I create a sample.

Here's my solution.

1.Create a new table with the time frame you want to display in the chart, don't make relationship between the two tables.

2.Create a measure.

Count =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[bid_short_date] <> BLANK ()
            && 'Table'[bid_short_date] <= EOMONTH ( MAX ( 'Table (2)'[Month] ), 0 )
            && IF ( ISBLANK ( 'Table'[Date_own_lost] ), TODAY (), 'Table'[Date_own_lost] )
                >= MAX ( 'Table (2)'[Month] )
    )
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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