Create new table with calculated columns and current date

[Anonymous]

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

[v-frfei-msft]

Hi @Anonymous,

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]
)

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

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

Regards,
Frank

[Anonymous]

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