Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

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


    Regards,
    Frank

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Anonymous,

       

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


      Regards,
      Frank

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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