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
Crazydog
Frequent Visitor

I am not sure how to create a logic for this

A bit new to PBI. I want to create to Montly performance tracker ( # of issue active, closed, resolved per month), result will be as I shown in result table. And Data is looking like something I have in data table. Thanks in advance! 

 

*Data Table*

Crazydog_0-1666380796665.png

 

 

 

*Result Table*

Issue Status       Current monthPast 1 monthPast X month 
# of Active issues            145
#Resovled Issues             234

 

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

Hi @Crazydog ,

 

You can try this method:

Sample data is your data table and because of the data's date, I assume that the current data is July,2022.

First, calculate the month and the year of the date:

vyinliwmsft_0-1666588588152.png

New Column:

Month = MONTH('Sample Table'[Date])
Year = YEAR('Sample Table'[Date]) 

Then create a new table:

Issue Table:

Issue Table = SUMMARIZE('Sample Table','Sample Table'[Issue Status])

vyinliwmsft_1-1666588688070.png

Then new columns:

Current Month =
CALCULATE (
    COUNT ( 'Sample Table'[Issue Status] ),
    FILTER (
        'Sample Table',
        'Issue Table'[Issue Status] = 'Sample Table'[Issue Status]
            && 'Sample Table'[Month] = 7
            && 'Sample Table'[Year] = 2022
    )
)

In this sample, month is 7 and year is 2022, you can change them to the data you need, use the function like Today().

Past 1 Month =
CALCULATE (
    COUNT ( 'Sample Table'[Issue Status] ),
    FILTER (
        'Sample Table',
        'Issue Table'[Issue Status] = 'Sample Table'[Issue Status]
            && 'Sample Table'[Month] = 7 - 1
            && 'Sample Table'[Year] = 2022
    )
)
Past X Month =
CALCULATE (
    COUNT ( 'Sample Table'[Issue Status] ) - SUM ( 'Issue Table'[Current Month] )
        - SUM ( 'Issue Table'[Past 1 Month] ),
    FILTER (
        'Sample Table',
        'Issue Table'[Issue Status] = 'Sample Table'[Issue Status]
    )
)

The result is:

vyinliwmsft_2-1666588944888.png

The Past 1 Month is blank because the sample data don't have the June data.

If I change the data in the code like this:

Past 1 Month =
CALCULATE (
    COUNT ( 'Sample Table'[Issue Status] ),
    FILTER (
        'Sample Table',
        'Issue Table'[Issue Status] = 'Sample Table'[Issue Status]
            && 'Sample Table'[Month] = 4 - 1
            && 'Sample Table'[Year] = 2021
    )
)

vyinliwmsft_3-1666589063949.png

There is the result of this column.

 

Is this what you expect?

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Crazydog
Frequent Visitor

What am I doing wrong here ? 


Current month =
CALCULATE (
    COUNT (v_issues_all_months_workorders[issue status]),
    FILTER (
        'v_issues_all_months_workorders',
        'Issue_Table_1'[issue status] = v_issues_all_months_workorders[issue status]
            && v_issues_all_months_workorders[Month] = MONTH(TODAY()-1)
            && v_issues_all_months_workorders[Year] = YEAR(TODAY())
    )
)

I am looking for the month - 1 from today and hence I did "MONTH(TODAY()-1)" but I am not getting correct result. Any help is appreciated! 

v-yinliw-msft
Community Support
Community Support

Hi @Crazydog ,

 

You can try this method:

Sample data is your data table and because of the data's date, I assume that the current data is July,2022.

First, calculate the month and the year of the date:

vyinliwmsft_0-1666588588152.png

New Column:

Month = MONTH('Sample Table'[Date])
Year = YEAR('Sample Table'[Date]) 

Then create a new table:

Issue Table:

Issue Table = SUMMARIZE('Sample Table','Sample Table'[Issue Status])

vyinliwmsft_1-1666588688070.png

Then new columns:

Current Month =
CALCULATE (
    COUNT ( 'Sample Table'[Issue Status] ),
    FILTER (
        'Sample Table',
        'Issue Table'[Issue Status] = 'Sample Table'[Issue Status]
            && 'Sample Table'[Month] = 7
            && 'Sample Table'[Year] = 2022
    )
)

In this sample, month is 7 and year is 2022, you can change them to the data you need, use the function like Today().

Past 1 Month =
CALCULATE (
    COUNT ( 'Sample Table'[Issue Status] ),
    FILTER (
        'Sample Table',
        'Issue Table'[Issue Status] = 'Sample Table'[Issue Status]
            && 'Sample Table'[Month] = 7 - 1
            && 'Sample Table'[Year] = 2022
    )
)
Past X Month =
CALCULATE (
    COUNT ( 'Sample Table'[Issue Status] ) - SUM ( 'Issue Table'[Current Month] )
        - SUM ( 'Issue Table'[Past 1 Month] ),
    FILTER (
        'Sample Table',
        'Issue Table'[Issue Status] = 'Sample Table'[Issue Status]
    )
)

The result is:

vyinliwmsft_2-1666588944888.png

The Past 1 Month is blank because the sample data don't have the June data.

If I change the data in the code like this:

Past 1 Month =
CALCULATE (
    COUNT ( 'Sample Table'[Issue Status] ),
    FILTER (
        'Sample Table',
        'Issue Table'[Issue Status] = 'Sample Table'[Issue Status]
            && 'Sample Table'[Month] = 4 - 1
            && 'Sample Table'[Year] = 2021
    )
)

vyinliwmsft_3-1666589063949.png

There is the result of this column.

 

Is this what you expect?

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is great! I appereciate your help, efforts and time you taken! 

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

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.