November 10, 2021 at 4:50 pm
Heloo all. Looking for help with some calculated measures. See the attached code below. It runs, but isn't returning the expected data results. What I am trying to get to with the calculated measures is this:
[Measures].[Next 7 Days of POs] = day 0 (current day) to day 7,
[Measures].[Next 14 Days of POs] = day 8 to day 14
[Measures].[Next 21 Days of POs] = day 15 to day 21
[Measures].[Next 28 Days of POs] = day 22 to day 28
[Measures].[Greater than 28 Days of POs] > 28 days
Appreciate any guidance. I do need to keep this in one query.
WITH
MEMBER [Measures].[Next 7 Days of POs] AS
SUM(LASTPERIODS(-7, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y] )
(0).Lag(-7)),[Measures].[Quantity Open - P])
MEMBER [Measures].[Next 14 Days of POs] AS
SUM(LASTPERIODS(-7, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
(0).Lag(-7)),[Measures].[Quantity Open - P])
MEMBER [Measures].[Next 21 Days of POs] AS
SUM(LASTPERIODS(-7, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
(0).Lag(-21)),[Measures].[Quantity Open - P])
MEMBER [Measures].[Next 28 Days of POs] AS
SUM(LASTPERIODS(-28, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
(0).Lag(-7)),[Measures].[Quantity Open - P])
MEMBER [Measures].[Greater than 28 Days of POs] AS
SUM(LASTPERIODS(-700, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
(0).Lag(0)),[Measures].[Quantity Open - P])
MEMBER [Measures].[Past Due POs] AS
SUM(LASTPERIODS(700, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
(0)),[Measures].[Quantity Open - P])
SELECT NON EMPTY
{
[Measures].[Quantity Open - P],
[Measures].[Quantity On Hand - I],
[Measures].[Next 7 Days of POs],
[Measures].[Next 14 Days of POs],
[Measures].[Next 21 Days of POs],
[Measures].[Next 28 Days of POs],
[Measures].[Greater than 28 Days of POs],
[Measures].[Past Due POs]
}
ON COLUMNS, NON EMPTY {(
[Items].[Item Number 02].CHILDREN,
[Item Branch].[Business Unit Code].CHILDREN
) }
ON ROWS FROM ( SELECT ( {
[Items].[SRP 1 - Type].&[Parts & Accessories]
} )
ON COLUMNS FROM ( SELECT ( {
[Item Branch].[Business Unit Code].&[ P1],
[Item Branch].[Business Unit Code].&[ DMT]
} )
ON COLUMNS FROM [Buyer]))
WHERE (
[Item Branch].[Item Number 02].&[5273630]
)
November 11, 2021 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply