Calculate Latest Transaction Date Time

[Anonymous]

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)

[lbendlin]

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