Forum Discussion

ngocrin1's avatar
ngocrin1
Frequent Visitor
3 years ago
Solved

Create a virtual table with Previous date transaction column

Hi everyone, I have problem with creating a Virtual table with Previous date transaction column. Has everyone can help me? The formula to get the Previous date column is below:     But the r...
  • tamerj1's avatar
    3 years ago

    Hi ngocrin1 
    Actually I did not get the idea of using 'Date'[Date] to obtain the previous date. If you want to get pervious calendar date then simply use

    Virtual Table =
    ADDCOLUMNS (
        SUMMARIZE ( Sales, Sales[Purchase Date], Sales[Sales Person ID] ),
        "@Revenue", [Sales Revenue],
        "@PreDate", Sales[Purchase Date] - 1
    )

    If you're looking for the previous sales date of the product in row then please try

    Virtual Table =
    ADDCOLUMNS (
        SUMMARIZE ( Sales, Sales[Purchase Date], Sales[Sales Person ID] ),
        "@Revenue", [Sales Revenue],
        "@PreDate",
            VAR CurrentDate = Sales[Purchase Date]
            VAR PreDate =
                MAXX (
                    CALCULATETABLE (
                        VALUES ( Sales[Purchase Date] ),
                        Sales[Purchase Date] < CurrentDate
                    ),
                    Sales[Purchase Date]
                )
            RETURN
                PreDate
    )