I am having trouble getting accurate results using set analysis for a date range in a bar graph.
Results are good in a chart with the use of an if statement, but this does not produce the correct result when converted to a bar graph - hence the need to use set analysis.
This statement works in a chart:
if (RevenueRecognisedFiscDate >= Date#(vFromDate) and RevenueRecognisedFiscDate <=Date#(vToDate) ,
sum({$<[CategoryCode]={'EREV'},[Fiscal Year],[Selected Fiscal Month] >} [ActualCost])*-1).
This statement does not work? A result is produced but not the right one.
Sum({<RevenueRecognisedFiscDate={">=$(vFromDate)"} , RevenueRecognisedFiscDate={"<$(vToDate)"},
[CategoryCode]={'EREV'},[Fiscal Year],[Selected Fiscal Month] >} ActualCost) * -1
I have tried any conversion of the date i can find but have not ended up with the right result.
RevenueRecognisedFiscDate is a Date field in the Load statement and vFromDate and vToDate are declared as :
vFromDate=Date#(Min([Fiscal Period]),'YYYYMM')
vToDate=Date#(Min([Fiscal Period]),'YYYYMM')
Does anyone have any other ideas to get the right result in a bar chart?