Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have written a dollar expansion that returns the minium date from the 'master month date' list box, then subtracts 12 months.
='$(=DATE(ADDMONTHS(MIN(DATE([Master Month Date],'DD/MM/YYYY')),-12,1),'DD/MM/YYYY'))'
The above formula is in a text box and works
The next step is to fit this into set analysis:
Count(DISTINCT {1<[ClosedMonth]={">=$(=DATE(ADDMONTHS(MIN(DATE([Master Month Date],'DD/MM/YYYY')),-12,1),'DD/MM/YYYY'))"}>} ReferenceNumber)
What I would like to return is the data set from 12 months before the current minimum date selection.
Closed month is the dimension and the above count formula is the expression.
Is this possible?
Thanks,
Neil.
What is the Format of Closed Month?
Also, how are master month date and Closed Month related to each other? May be ignore selection in master month date
Count(DISTINCT {1<[ClosedMonth]={">=$(=Date(AddMonths(Min([Master Month Date]), -12, 1),'DD/MM/YYYY'))"}, [master month date] = >} ReferenceNumber)
Master month date is in my master calendar, it stores all month end dates for all transactions,
Therefore when selected this will link to closed month in the fact table to the Master calendar.
I have done this many times, but not tried to have the Master Month Date 12 months before.
an example is
Count(distinct {<[ResolvedMonth]=$::[Master Month Date],[Date Reference Info]={'Resolved Date'},OverallOutcome={'Upheld','Partially Upheld'}>} ReferenceNumber)