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

[JKMF]

Hi, 

I am new to this.
I would like to duplicate the same dataset using historical data. Currently, I have data from row 1 to row 7 and column A B C.

Row 8 to row 13 are the data i want to duplicate using the row 2 to row 7 data (e.g. 31 Mar 22 is using 31 Dec 21, 31 Mar 23 is using 31 Dec 22, etc.,).

Appreciate your help in this.

As the number of entities may be different from Period to Period (e.g. Dec 2021 has 20 entities, whilst Dec 22 has 19 entities and Dec 23 has 24 entities), what should be the formula written in power BI. Thank you!

[Jihwan_Kim · Accepted solution]

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 )
        )
    )

[v-pagayam-msft]

Hi @JKMF ,

Glad that your query has been resolved. If our community member's response addressed your question, please Accept it as  Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[v-pagayam-msft]

Hi @JKMF ,

Glad that your query has been resolved. If our community member's response addressed your question, please Accept it as  Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[pcoley]

@JKMF It seems like you should try powerquery, not dax. 
You may have historical data from the actual year that can be used as "duplicate rows for a new quarter", but how do you expect to populate the revenue for the new entities that don´t have historical records?

As a first approach, I suggest to create a table in your excel with the historical data, copying and using a "new blank query" to paste the following code into your PowerQuery advance editor.

please change "YOUR PATH" with the path in wich you localy have the file (including the name and type file, keep the apostrophes " ")
please change "YOUR SHEET" with the name of the sheet where you placed the table, keep the apostrophes " ". 
Please have this names for each header in the table: Period | Entity | Revenue | status

let
  Source = Table.PromoteHeaders(
    Excel.Workbook(File.Contents("YOUR PATH"), null, true){[Item = "YOUR SHEET", Kind = "Sheet"]}[Data], 
    [PromoteAllScalars = true]
  ), 
  Type = Table.TransformColumnTypes(
    Source, 
    {{"Period ", type date}, {"Entity", type text}, {"Revenue", Int64.Type}, {"status", type text}}
  ), 
  AddMonths = Table.TransformColumns(Type, {{"Period ", each Date.AddMonths(_, 3)}}), 
  ChangeStatus = Table.ReplaceValue(
    AddMonths, 
    "Available data", 
    "Created for proxy", 
    Replacer.ReplaceText, 
    {"status"}
  ), 
  Combine = Table.Combine({Type, ChangeStatus})
in
  Combine

Hope this helps. 
Please let me know if it was helpful. If yes, please Accept the answer. kudos are accepted😀

[v-pagayam-msft]

Hi @JKMF ,
I just wanted to kindly follow up to see if you had a chance to review the previous response provided by me and @Jihwan_Kim . Please let me know if it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.Thank you.

[v-pagayam-msft · Accepted solution]

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.

[Jihwan_Kim · Accepted solution]

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 )
        )
    )

[danextian]

Hi @JKMF 

What do you mean by  this?
Row 8 to row 13 are the data i want to duplicate using the row 2 to row 7 data (e.g. 31 Mar 22 is using 31 Dec 21, 31 Mar 23 is using 31 Dec 22, etc.,).

Can you please post a workable sample data and not an image and your expected result from that?