cube structure question two measure groups not tied

Question

here is another tricky one

i have many insurance policies each has a weekly record with transaction date (Sunday) and two measures - premium that week and Inforce indicator = 1 if on that weekend policy still alive

on a given date i need to get the number of policies that are still alive and the total of premiums for them

so basically first i need a set of policies on that date that have inforce = 1
then for each policy i need to aggregate along time axis all premiums up that date

clearly slicing the cube can be done in subselect so that week's record is the last for each policy
here is my first humble try i use where instead of subselect:

SELECT FILTER
(
[Dim Policy].[Policy Nbr].Members,
([Txn Time].[Full Date].[2020-10-23T00:00:00],
[Measures].[Inforce]) > 0
) ON 0
FROM from [Bridger Olap All]
WHERE [Txn Time].[Full Date] -lt [Txn Time].[Full Date].[2020-10-23T00:00:00]

here i want to first get on columns all relevant policies with that (default) measure Premium

Thanks!!!!

!!!!!!!!!!!!!!! where can I find a solid pro and pay them for help?????????????!!!!!!!!!!!!!!!
on likes of upwork they are no help

Answer 1

...WHERE NULL:[Txn Time].[Full Date].[2020-10-23T00:00:00] looks more like it

Answer 2

Alexei

however i do have a question: what if the input date is NOT in that Txn Time table?

say if it is a true datetime with real minutes?
then [Txn Time].[Full Date].[2020-10-23T04:44:13] is NOT in the table
but i still need to force inequality less than??????
can we treat some date hierarchy as truly ordered

Answer 3

sorry i mistyped
it is null:[Txn Time].[Hierarchy].[Full Date].&[2022-10-23T00:00:00]

but again what i need ultimately is
[Txn Time].[Hierarchy].[Full Date]...... < some totally random CDate()
basically filter by CDate the piece of cube BEFORE that CDAte

is that possible canot make it work