Forum Discussion
JordanBI
2 years agoNew Member
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
- Ashish_Mathur
Super User
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.