Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kostas
Helper IV
Helper IV

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

@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
AlB
Super User
Super User

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

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

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

Capture12.PNG

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.