Forum Discussion

JordanBI's avatar
JordanBI
New Member
2 years ago

Limiting date range on a cumulative chart

Hello,

 

I have a model with a date table, and a data table with measures "cash in" - "cash out" simulate the monthly instantaneous cashflow.

I'm looking to represent it in a chart in a cumulative way. I've worked it many ways, and I've got the expected result but the chart is showing all dates from the date table, whereas I'd like to limite to the last date of my data measure + x months (for the payment term) :

 

Cash_flow = 
VAR nbmonth = IF('Payment terms'[Payment terms Value]<=30, 1, IF('Payment terms'[Payment terms Value] <= 60,2 , 3))
VAR latest_deal_revenue = MAX(DimDates[Date])
VAR latest_deal_invoice = DATE(YEAR(latest_deal_revenue),MONTH(latest_deal_revenue)+nbmonth,DAY(latest_deal_revenue))
VAR Cumulative_amount = CALCULATE([Cash_in]+[Cash_out],DimDates[Date] <= latest_deal_invoice,CROSSFILTER(DimDates[Date],Data[DateLink],Both))

RETURN
Cumulative_amount

 

Plus the query is very slow so any improvement on this would be great !

Any idea how I could limit the graph to the expected dates ?

1 Reply

  • Hi,

    Share data in a format that can be pasted in an MS Excel file and show the expected result in a simple Table format.