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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.