Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am stuck in a formula to calculate the YTM data for my sales, i created four variables stating the respective dates (beginning and ending of my selection) and (the beginning and ending of my selection minus 1 year). The Measure currently looks like:
Count( {1<[ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"}>} Distinct( ReservationNumber))
I have two issues:
1. The measure doesn't take any other filter into account (obviously because of the identifier 1) So I am looking for the statement so that my measure looks at all the "ReferenceDate.Calendar" fields but still takes any other filter into account (without me specifying all the different fields like Name=$::Name (with "Name" a dimension")
2. If i omit a month in my selection, the measurement doesn't do the same. Could I change my ">=" & "<" to the variables to a set of data instead
the variable looks like:
NUM(IF( GETSELECTEDCOUNT([ReferenceDate.Calendar.Year])=0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.Month]) = 0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.Date])=0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.DateNum])=0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.FiscalYear])=0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.MonthFY])=0,
$(vT_DateMaxYTMmin1), MONTHEND(MAX(Addmonths([ReferenceDate.Calendar.Date],-12)))))
Thanks for helping me out,
Yorick
Hi All,
For persons who are stuck with the same issue.
The solution is:
Count( {$< [ReferenceDateNum] = P( {1<[ReferenceDateNum]={">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"} >} [ReferenceDateNum]),
[ReferenceMonth] = P( {1<[ReferenceDateNum]={">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"} >} [ReferenceMonth])>}
Total< [ReferenceDateNum],
[ReferenceMonth]> Distinct ( ReservationNumber))
My Mesure takes a look at all filters except the for 'ReferenceDateNum' and 'ReferenceMonth' still takes into account the specific filter on ReferenceDateNum.
Hoping this helps anyone.
Few comments like
1) Count( {1<[ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"}, %Key=$::[ReferenceDate.Calendar*]>} Distinct( ReservationNumber))
or
2) Quite okay to me, What is your intention here?
Hi All,
For persons who are stuck with the same issue.
The solution is:
Count( {$< [ReferenceDateNum] = P( {1<[ReferenceDateNum]={">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"} >} [ReferenceDateNum]),
[ReferenceMonth] = P( {1<[ReferenceDateNum]={">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"} >} [ReferenceMonth])>}
Total< [ReferenceDateNum],
[ReferenceMonth]> Distinct ( ReservationNumber))
My Mesure takes a look at all filters except the for 'ReferenceDateNum' and 'ReferenceMonth' still takes into account the specific filter on ReferenceDateNum.
Hoping this helps anyone.