cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Counting items from two date columns in one single timeline

Hello,

I'm trying to plot a table where I can observe on a timeline (months), how many items are between two given dates (start & end) from a table. The problem I'm having is that these two dates are rperesented in two different columns and I can't find a good way of relating them to a Calendar table in order to get what I want. Example:

TableX

 User Project StartDate EndDate A Project1 10/5/2021 12/7/2021 A Project2 4/21/2021 7/28/2021 B Project3 5/23/2021 9/13/2021 B Project4 7/18/2021 11/6/2021 B Project5 11/7/2021 11/18/2021 C Project6 8/16/2021 12/28/2021 C Project7 11/13/2021 12/4/2021

From TableX I'd like to tell on a given month, how many projects would the person have assigned, and observe it by month. Liek this:

Result

 User Total Projects Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec A 2 0 0 0 1 1 1 1 0 0 1 1 1 B 3 0 0 0 0 1 1 2 2 2 1 2 0 C 2 0 0 0 0 0 0 0 1 1 1 2 2

Would this be even possible?

Thanks a lot.

1 ACCEPTED SOLUTION
Super User

@aridavid
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@aridavid
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Works like a charm, that's so clever! I was busting my brain trying relationships... Thanks @Greg_Deckler !