cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kostas Frequent Visitor
Frequent Visitor

Show count of subjects within a date period in visual

Hello,

In regards to my problem I found one solution in the forum: 

https://community.powerbi.com/t5/Desktop/Count-no-of-record-with-date-range-within-a-period/m-p/1568...

but unfortunatelly I could not make it work. 

I have a table with a column that contains multiple business funtions that run for a specific period of time. I also have a column with the start date of each funtion and an end day. Capture11.PNG

I need so show with a visual the bussiest dates within the year by counting the business functions that "run" within the specific month. Maybe with a area chart. 

I already created a graph but in those you allowed to input only one date value and not a range as you can see below.Capture12.PNG

 

Thank you in advance 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Show count of subjects within a date period in visual

@Kostas 

That's the ALL in FILTER. It's cancelling the business unit filter in the legend. Just take it out. And I forgot to mention that there is no relationship between Calendar and your fact table

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Function] ),
    FILTER (
        Table1,
        NOT (
            Table1[Start Date] > MAX ( Calendar[Date] )
                || Table1[End Date] < MIN ( Calendar[Date] )
        )
    )
)

 

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Show count of subjects within a date period in visual

Hi @Kostas 

Try this measure:

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Function] ),
    FILTER (
        ALL ( Table1 ),
        NOT (
            Table1[Start Date] > MAX ( Calendar[Date] )
                || Table1[End Date] < MIN ( Calendar[Date] )
        )
    )
)

 Where you need a Calendar table whose MonthYear column would be used in the x-axis of your visual

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

Kostas Frequent Visitor
Frequent Visitor

Re: Show count of subjects within a date period in visual

Hello! 

It seems that is working perfectly, I got one more question. 

If in the same table have one more column that separates the business functions as categories, how I could include that in the chart as legend. I tried it with the current measure but it seems that it picks the same count of business funtions for all the Business Units. Capture11.PNGCapture12.PNG

 

Thank you again for your help

 

Kind Regards

Kostas Frequent Visitor
Frequent Visitor

Re: Show count of subjects within a date period in visual

I am sorry the picture from the graph added before is not contain the correct data in the legend.

Capture12.PNG

Super User III
Super User III

Re: Show count of subjects within a date period in visual

@Kostas 

That's the ALL in FILTER. It's cancelling the business unit filter in the legend. Just take it out. And I forgot to mention that there is no relationship between Calendar and your fact table

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Function] ),
    FILTER (
        Table1,
        NOT (
            Table1[Start Date] > MAX ( Calendar[Date] )
                || Table1[End Date] < MIN ( Calendar[Date] )
        )
    )
)

 

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors