Hi
I am a newbe in DAX and i have a issue in figuring out how to correctly filter my data. This is the issue. I have a set of invoices that go through a set of steps during processing. in a day they can go through multiple steps. I am trying to figure out how many distinct invoices at a given day (selected by a user) i have in that day. I am using SSAS in direct query mode.
I can figure this out perfectly in SQL but in DAX i have not been able to figure this out.
This is the data set i use for testing
InvoiceNumber | ProcessStep | StartDate | EndDate | TimeOrder |
200 | Step 1 | 2020-03-30 | 2020-04-01 | 1 |
200 | Step 2 | 2020-04-01 | 2020-04-01 | 2 |
200 | Step 3 | 2020-04-01 | 2020-04-06 | 3 |
200 | Step 4 | 2020-04-07 | NULL | 4 |
300 | Step 1 | 2020-04-01 | 2020-04-01 | 1 |
300 | Step 2 | 2020-04-01 | 2020-04-04 | 2 |
300 | Step 3 | 2020-04-04 | 2020-04-06 | 3 |
300 | Step 4 | 2020-04-07 | 2020-04-09 | 4 |
300 | Step 3 | 2020-04-10 | NULL | 5 |
these are the results i get from my queries
InvoiceNumber | ProcessStep | StartDate | EndDate | TimeOrder |
200 | Step 3 | 2020-04-01 | 2020-04-06 | 3 |
300 | Step 3 | 2020-04-04 | 2020-04-06 | 3 |
Below i have created a small sample script for SQL to show how i do this in sql.
CREATE TABLE [dbo].[InvoiceProcess](
[InvoiceNumber] [int] NOT NULL,
[ProcessStep] [nvarchar](50) NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NULL,
[TimeOrder] [int] NOT NULL
) ON [PRIMARY]
GO;
TRUNCATE TABLE [dbo].[InvoiceProcess];
GO;
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (200, N'Step 1', CAST(N'2020-03-30' AS Date), CAST(N'2020-04-01' AS Date), 1)
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (200, N'Step 2', CAST(N'2020-04-01' AS Date), CAST(N'2020-04-01' AS Date), 2)
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (200, N'Step 3', CAST(N'2020-04-01' AS Date), CAST(N'2020-04-06' AS Date), 3)
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (200, N'Step 4', CAST(N'2020-04-07' AS Date), NULL, 4)
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 1', CAST(N'2020-04-01' AS Date), CAST(N'2020-04-01' AS Date), 1)
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 2', CAST(N'2020-04-01' AS Date), CAST(N'2020-04-04' AS Date), 2)
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 3', CAST(N'2020-04-04' AS Date), CAST(N'2020-04-06' AS Date), 3)
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 4', CAST(N'2020-04-07' AS Date), CAST(N'2020-04-09' AS Date), 4)
INSERT [dbo].[InvoiceProcess] ([InvoiceNumber], [ProcessStep], [StartDate], [EndDate], [TimeOrder]) VALUES (300, N'Step 3', CAST(N'2020-04-10' AS Date), NULL, 5)
GO;
DECLARE @DATE DATE = '2020-04-04';
WITH LastInvoice
AS (
SELECT InvoiceNumber,max(TimeOrder) TimeOrder
FROM InvoiceProcess
WHERE @DATE between StartDate and EndDate
GROUP BY InvoiceNumber
)
SELECT IP.InvoiceNumber
, IP.ProcessStep
, IP.StartDate
, IP.EndDate
, IP.TimeOrder
FROM InvoiceProcess IP
INNER JOIN LastInvoice LI on IP.InvoiceNumber = LI.InvoiceNumber
and IP.TimeOrder = LI.TimeOrder
ORDER BY
IP.ProcessStep
,IP.StartDate;
WITH LastInvoice
AS (
SELECT InvoiceNumber,max(TimeOrder) TimeOrder
FROM InvoiceProcess
WHERE @DATE between StartDate and EndDate
GROUP BY InvoiceNumber
)
SELECT
IP.ProcessStep
, COUNT(DISTINCT IP.InvoiceNumber)
FROM InvoiceProcess IP
INNER JOIN LastInvoice LI ON IP.InvoiceNumber = LI.InvoiceNumber
AND IP.TimeOrder = LI.TimeOrder
GROUP BY IP.ProcessStep
ORDER BY
IP.ProcessStep;
I would be very much obliged if someone could help me figure this out in DAX?
Kind regards
Remond
