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 a...
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.

     

     

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