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 result is still blank.

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

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    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
    )
    • ngocrin1's avatar
      ngocrin1
      Frequent Visitor

      Tks Tamerj1,

      It worked well. I'm looking for previous date sales transactions.

  • some_bih's avatar
    some_bih
    Community Champion

    Hi ngocrin1 please try to change your date column in Power Query from type date time to type date, and see results. I hope this help