Previous and Current Date Value Comparison

[cruzp]

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

[Anonymous · Accepted solution]

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.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Anonymous]

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]

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