cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kushagravijay Frequent Visitor
Frequent Visitor

Create new table with calculated columns and current date

Hi, I have a table with Status column having different statuses like 'Deployed', 'End of Life', 'Ordered', etc. The source table gets updated daily along with status of existing values but their is no date column to measure when the status got modified. I am trying to build a report which gives me count of each status on a particular date by either comparing from previous run or put a date column in new table with count of each status. For Example:

 

Original Table:

Status Column having multiple value of statuses like 'Deployed', 'End of Life', 'Ordered', 

 

New Table Data: 

Date: 10/28/2018 (when report is run)

Count of Deployed Status: 100

Count of End of life Status: 300

Count of Ordered Status: 150

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create new table with calculated columns and current date

Hi @kushagravijay,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

Community Support Team _ Frank
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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Create new table with calculated columns and current date

Hi @kushagravijay,

 

We can create a calculated table using the formula.

 

Table2 = 
SUMMARIZE (
    ADDCOLUMNS (
        'source table',
        "date", TODAY (),
        "Deployed", CALCULATE (
            COUNTA ( 'source table'[Status] ),
            FILTER ( 'source table', 'source table'[Status] = "Deployed" )
        ),
        "End of Life", CALCULATE (
            COUNT ( 'source table'[Status] ),
            FILTER ( 'source table', 'source table'[Status] = "End of Life" )
        ),
        "Ordered", CALCULATE (
            COUNTA ( 'source table'[Status] ),
            FILTER ( 'source table', 'source table'[Status] = "End of Life" )
        )
    ),
    [date],
    [Deployed],
    [End of Life],
    [Ordered]
)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

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

Re: Create new table with calculated columns and current date

Hi @kushagravijay,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.


Regards,
Frank

Community Support Team _ Frank
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

kushagravijay Frequent Visitor
Frequent Visitor

Re: Create new table with calculated columns and current date

Hi @v-frfei-msft thanks a lot for providing the solution and your help. It worked! 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 56 members 1,290 guests
Please welcome our newest community members: