Calculate Latest Transaction Date Time

Anonymous — Wed, 20 Dec 2023 12:45:34 GMT

Need the correct way to Calculate LastTransaction Date Time

--What i have written, Time also should be added to date.

LastTransactionDateTime = CALCULATE( max((D_SalesHeader[SalesTransactionDate])))

I want Last Transaction Date Time (Date+Time) a meausre, Dont want to create calculated column as data is very big in actual model.

Showng wrong Value

Expected Result

--Create Table Scripts

--USE DB
GO
Create Schema PowerBiTest

DROP TABLE IF EXISTS PowerBiTest.D_SalesHeader
DROP TABLE IF EXISTS PowerBiTest.D_Store
DROP TABLE IF EXISTS PowerBiTest.D_Date
DROP TABLE IF EXISTS PowerBiTest.D_Customer
DROP TABLE IF EXISTS PowerBiTest.F_Sales

CREATE TABLE PowerBiTest.D_SalesHeader
(
BK_HeaderID INT
,SalesTransactionDate Date
,SalesTrnsactionTime Time
)
CREATE TABLE PowerBiTest.D_Store
(
BK_StoreName Varchar(100)
)
CREATE TABLE PowerBiTest.D_Date
(
BK_DATE Date
)
CREATE TABLE PowerBiTest.D_Customer
(
BK_CustName Varchar(200)
)
CREATE TABLE PowerBiTest.F_Sales
(
FK_HeaderID INT
,FK_StoreID VARCHAR(100)
,FK_DateID DATE
,FK_CustID Varchar(200)
,LineId INT
,SaleAmount MONEY
)

INSERT INTO PowerBiTest.D_SalesHeader(BK_HeaderID,SalesTransactionDate,SalesTrnsactionTime)
VALUES(101,'20231201','10:44:57.6133333'),
(102,'20231201','11:10:15.4115111'),
(103,'20231201','13:22:45.1211000'),
(104,'20231202','11:00:00.4115111'),
(105,'20231202','15:15:00.2115111'),
(106,'20231203','17:01:00.3312444')

INSERT INTO PowerBiTest.D_Store(BK_StoreName)
VALUES('HYD'),('BLR'),('CHN')

INSERT INTO PowerBiTest.D_Customer(BK_CustName)
VALUES('AAA'),('BBB'),('CCC'),('DDD'),('EEE')

INSERT INTO PowerBiTest.D_Date(BK_DATE)
VALUES('20231201'),('20231202'),('20231203'),('20231204'),('20231205')

INSERT INTO PowerBiTest.F_Sales
VALUES(101,'HYD','20231201','AAA',1,10.00),
(101,'HYD','20231201','AAA',2,20.00),
(103,'HYD','20231201','BBB',1,25.00),
(104,'BLR','20231202','BBB',1,45.00),
(105,'CHN','20231202','CCC',1,45.00),
(106,'HYD','20231203','AAA',1,50.00)

Re: Calculate Latest Transaction Date Time

lbendlin — Sat, 23 Dec 2023 22:27:13 GMT

You are trying to "filter up" from the sales fact table into the sales header dimension table. It would be much simpler to add the transaction timestamp to the fact table.

see attached

topic Calculate Latest Transaction Date Time in DAX Commands and Tips

Calculate Latest Transaction Date Time

Re: Calculate Latest Transaction Date Time