Help creating a table of count measures across columns of time periods

[jwest11]

Hi there,

I am quite new to Power BI and trying to figure out how to do something that I can do fairly easily in Excel in Power BI.

The dataset I'm working with is one that contains milestone dates data for a customer, where each row is a distinct customer & each column is a different milestone data. See attached screenshot

The end product that I'm trying to build is a table where each row corresponds to a milestone, and each column is a time period, and the value in the cell is count of how many customers hit the respective milestone in the respective time period. In Excel, I can easily build this with countif functions as seen in the screenshot below

How would I do this in Power BI? My gut is telling me that I would most likely create a calendar/date table and establish a relationship between it and my dataset, but I'm very new to the platform and could be wrong.

Thank you very much in advance for your help!

[v-yanjiang-msft · Accepted solution]

Hi @jwest11 ,

According to your description, in my understanding, you want to get the count of project id for each milestone data in the last 7 days, if I understand correctly, here's my solution.

1.Create a new date table by click the New table tab.

Date = CALENDAR(DATE(2021,12,1),DATE(2022,12,31))

2.Put the date column in a slicer and selecte Relative Date type, choose last 7 days. The slicer will always show the last 7 days from today automatically.

3.Create measures.

Closer Appointment Held(Count) = 
CALCULATE (
    COUNT ( 'Table'[Project ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Closer Appointment Start] = MAX ( 'Date'[Date] )
    )
) + 0

Installation Agreement Signed(Count) = 
CALCULATE (
    COUNT ( 'Table'[Project ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Installation Agreement Signed] = MAX ( 'Date'[Date] )
    )
) + 0

Financial Agreement Signed(Count) = 
CALCULATE (
    COUNT ( 'Table'[Project ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Financial Agreement Signed] = MAX ( 'Date'[Date] )
    )
) + 0

4.Put date column and the measures in a table visual, get the 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.

[Greg_Deckler]

@jwest11 My gut is telling me that you are likely going to want to unpivot your date columns.