How to duplicate same dataset using historical data, as a proxy for current date?

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

expected result measure: =
VAR _threemonthsbefore =
    EOMONTH ( MAX ( period[period] ), -3 )
RETURN
    SUMX (
        SUMMARIZE ( sales, period[period], entity[entity] ),
        IF (
            NOT ISBLANK ( CALCULATE ( SUM ( sales[revenue] ) ) ),
            CALCULATE ( SUM ( sales[revenue] ) ),
            CALCULATE ( SUM ( sales[revenue] ), period[period] = _threemonthsbefore )
        )
    )

Hi JKMF ,

Thank you for reaching out to the Microsoft Fabric Community Forum regarding the issue you're facing.

After reviewing the screenshot provided, I attempted to recreate the scenario locally with sample data which might help you:

Select the  HistoricalData and CurrentData tables.
Then,create a DataMapping table to link the CurrentData table to the HistoricalData table.
Later established relationships between the tables based on the relevant columns.
In the CurrentData table, create a new following measure to retrieve the corresponding historical values.

New =
CALCULATE(
MAX('HistoricalData'[Value]),
FILTER(
'HistoricalData',
'HistoricalData'[Date] = RELATED('DateMapping'[HistoricalDate]) &&
'HistoricalData'[Entity] = 'CurrentData'[Entity]
)
)

Afterthat, go to the report and create a Table visual.
Include Date, Entity, and the NewValue field from the CurrentData table.
I have attached a screenshot for your reference.

If this solution helps, please consider giving us a "Kudos" and mark this post as the solution. This will help other members find it more quickly.