Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
)
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
)
Tks Tamerj1,
It worked well. I'm looking for previous date sales transactions.
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |