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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ljmgordon
Frequent Visitor

Table to show count by month

HI, folks,

 

I am certain my question is stupidly easy, but I've checked with two colleagues already who are far more proficient than me and I'm really struggling. I have a table called 'Data' with 5122 rows that looks like this (yes one error can have multiple rows, and same employee can be covered by multiple errors, not typos):

Error #Employee ID #Date
HR1101/01/2023 06:05:27
HR2201/01/2023 12:49:13
HR3201/02/2023 07:25:46
HR3301/02/2023 07:25:46
HR44

01/03/2023 19:21:37

......

...

HRnxyz

29/06/2023 23:56:24

 

I'm trying to get a measure to show count of rows for last month, and another for two months ago, for a leadership dashboard to show progress on projects to drive down error volume.  I've searched on the forums and found loads of helpful things to try which haven't worked for me, and one thing I tried was to build a table that shows # of cases per month:

 

ErrorsByMonth = 
addcolumns(
    distinct(
        SELECTCOLUMNS(
            calendarauto(), 
            "Date", DATE (YEAR ( [Date] ), MONTH ( [Date] ), 1)
        )
    ), 
    "Count", 0 
        + COUNTROWS ( 
            FILTER ( 
                Data, 
                YEAR ( DATA[Date] ) = YEAR ( [date] ) 
                && MONTH ( DATA[Date] ) = MONTH ( [date] )
            )
        )
 )

 

However, when I do that, the result is this: 

DateCount
01/01/2023 00:00:005122
01/02/2023 00:00:005122
01/03/2023 00:00:005122
01/04/2023 00:00:005122
01/05/2023 00:00:005122

 

I'm obviously doing something stupid but I cannot fathom what. Things I've checked: column 'Date' in my table is definitely defined as a date column. I tried adding a date table and creating the relationship, also failed.

 

I have a visual with a line graph of count per month that works fine, but for the "executive summary" page of my report I just want a big number that says : "500 errors last month vs 600 previous month" that refreshes as we load new data. I feel like if I can crack this table, I've cracked my entire issue. 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @ljmgordon ,

 

Please try:

ErrorsByMonth 2 = 
ADDCOLUMNS (
    DISTINCT (
        SELECTCOLUMNS (
            CALENDARAUTO (),
            "Date", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        )
    ),
    "Count",
        CALCULATE (
            COUNTROWS ( 'Data' ),
            FILTER (
                'Data',
                YEAR ( 'Data'[Date] ) = YEAR ( EARLIER([Date]) )
                    && MONTH ( 'Data'[Date] ) = MONTH ( EARLIER([Date]) )
            )
        ) + 0
)

vcgaomsft_0-1690178606446.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @ljmgordon ,

 

Please try:

ErrorsByMonth 2 = 
ADDCOLUMNS (
    DISTINCT (
        SELECTCOLUMNS (
            CALENDARAUTO (),
            "Date", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        )
    ),
    "Count",
        CALCULATE (
            COUNTROWS ( 'Data' ),
            FILTER (
                'Data',
                YEAR ( 'Data'[Date] ) = YEAR ( EARLIER([Date]) )
                    && MONTH ( 'Data'[Date] ) = MONTH ( EARLIER([Date]) )
            )
        ) + 0
)

vcgaomsft_0-1690178606446.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

foodd
Super User
Super User

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).


https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...


Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

 

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.