Cumulative data in chart.

[agni_gold]

Hi All ,

I have a booking fact table in which some fields like , Segment and Date, and ..... so on columns present , now i have created one calendar and linked with fact table date.

Now i have created chart which is showing comparison between current year booking vs previous year booking.

but in case if we are selecting current month ,booking will happen till current date , and for previous year all days data present in table ,

i am using below expression for current booking expression and previous year booking expression,

Current year booking expression is

RangeSum(above(Sum({<[Segment Entity]-= {""},Date= {">=$(=Min([Month Start Date]))<=$(v_Date)"}> }[Sale Amount USD])/1000000,0,
  Sum({<Date= {">=$(=Min([Month Start Date]))<=$(v_Date)"}> }Day)) ) 

where v_Date is current Date.

Previous year booking expression

RangeSum(above(Sum({<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }[Sale Amount USD])/1000000,0,
  sum( {<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }Day)) )

It is working file when i am not applying any filter in dashboard except year and month, suppose i have applied filter in Segment field , previous year booking bars are also coming till date . like below

But i want till full month.

Please suggest where i am doing wrong.

[sunny_talwar]

May be this:

If(Sum({<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }[Sale Amount USD]) > 0, RangeSum(above(Sum({<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }[Sale Amount USD])/1000000,0,
  sum( {<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }Day)) ))

[jonathandienst]

The problem could be one of several causes:

• There is no data for the other days
• Some selection is suppressing the other days
• The variables used in the expression (for which you provided no information)

Having said that, I would also consider modifying the expressions to something like

RangeSum(Above(Sum({<[Segment Entity]-= {""},Date= {">=$(=Min([Month Start Date]))<=$(v_Date)"}> } [Sale Amount USD])/1000000, 0, RowNo()))

or

RangeSum(Above(Sum({<[Segment Entity]-= {""},Date= {">=$(=Min([Month Start Date]))<=$(v_Date)"}> } [Sale Amount USD])/1000000, 0, 31))

but I don't think that is your problem.

[agni_gold]

No Sunny , it is now working ,

actually when i am selecting segment then dates are also filtered and max possible date is gone till current date, i think because of this previous year date is also restricting ,

Please suggest.

[sunny_talwar]

then restrict the selection of segment on previous year dates.I guess I don't have the sample in front of me so can't really see and play around with it.

[agni_gold]

Sunny , if i restrict selection of segment then my requirement will not full fill,

Actually when i select any segment then based on that segment it should show comparison between current and previous year booking data.

Thanks

[trdandamudi]

Is it possible to post a small sample so that we can help you out...

[sunny_talwar]

Not restrict everywhere but where you are calculating the Max Date. May be like Max({<Segment>} Date)....

[agni_gold]

Hi Sunny , i have tried to make restriction , like below for v_prvmonthstart

=Date(min({<Segment=>}AddMonths([Month Start Date],-12)),'DDMMMYYYY')

and for v_prvmonthend

=Date(Max({<Segment=>}AddMonths([Month End Date],-12)),'DDMMMYYYY')

But still not working

[sunny_talwar]

Can you post images and show what is going on with and without selections (and what selections)