Forum Discussion

cruzp's avatar
cruzp
Icon for Helper V rankHelper V
2 years ago
Solved

Previous and Current Date Value Comparison

Hello PBI folks, 

 

I have a data in PBI that contains snapshots including movements of specific projects from the start up to date.

I want to have a comparison value that is based on what dates are selected in the two date slicers:

  • Old Value Date Slicer
  • New Value Date Slicer

Is there a way to transform the current table below into my expected output?

 

See sample project below:

 

Current table in PBI

The expected output for this sample project I want to achieve will be exactly like this:

 

Sample report is here:

https://www.dropbox.com/scl/fi/u0lm11smcrigr1zbiqw8k/sample.pbix?rlkey=78sv2o3o0h04obh6sutspw9lm&st=qkchzvlj&dl=0

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi cruzp 

     

    Please create measures.

     

    Stage = 
CALCULATE(
        SELECTEDVALUE('Table'[STAGE_NAME]),
        FILTER(
            ALL('Table'),
            'Table'[DATE] = MAX('New Date'[new date])
        )
)

     

    Old Value = 
CALCULATE(
    MAX('Table'[STAGE_NAME]),
    FILTER(
        ALL('Table'),
        'Table'[DATE] = MIN('Old Date'[Old date])
    )
)

     

    New Value = 
CALCULATE(
    MAX('Table'[STAGE_NAME]),
    FILTER(
        ALL('Table'),
        'Table'[DATE] = MAX('New Date'[new date])
))

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi cruzp 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    “Table”

     

    Create new tables.

     

    The date table is used to create the slicer.

     

    Old Date = SELECTCOLUMNS('Table', "Old date", 'Table'[DATE])

     

    New Date = SELECTCOLUMNS('Table', "new date", 'Table'[DATE])

     

    Here is the result table.

     

    Result Table = 
SUMMARIZE(
    'Table',
    "Project Code",
    SELECTEDVALUE('Table'[PROJECT_BK]),
    "Stage",
    CALCULATE(
        SELECTEDVALUE('Table'[STAGE_NAME]),
        FILTER(
            ALL('Table'),
            'Table'[DATE] = MAX('New Date'[new date])
        )
    ),
    "Start Date",
    SELECTEDVALUE('Table'[START_DATE]),
    "End Date",
    SELECTEDVALUE('Table'[END_DATE]),
    "Old Value",
     CALCULATE(
        SELECTEDVALUE('Table'[STAGE_NAME]),
        FILTER(
            ALL('Table'),
            'Table'[DATE] = MIN('Old Date'[Old date])
        )
    ),
    "New Value",    
     CALCULATE(
        SELECTEDVALUE('Table'[STAGE_NAME]),
        FILTER(
            ALL('Table'),
            'Table'[DATE] = MAX('New Date'[new date])
        )
    )
)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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

    • cruzp's avatar
      cruzp
      Icon for Helper V rankHelper V
      2 years ago

      Hi Anonymous thanks for the suggestion. is there a way to have it without using summarize table? like a more straight forward approach? because i will be adding more columns in the table

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi cruzp 

     

    Please create measures.

     

    Stage = 
CALCULATE(
        SELECTEDVALUE('Table'[STAGE_NAME]),
        FILTER(
            ALL('Table'),
            'Table'[DATE] = MAX('New Date'[new date])
        )
)

     

    Old Value = 
CALCULATE(
    MAX('Table'[STAGE_NAME]),
    FILTER(
        ALL('Table'),
        'Table'[DATE] = MIN('Old Date'[Old date])
    )
)

     

    New Value = 
CALCULATE(
    MAX('Table'[STAGE_NAME]),
    FILTER(
        ALL('Table'),
        'Table'[DATE] = MAX('New Date'[new date])
))

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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