Cumulative chart with empty data in some months

[JoaoMS]

Hi all, I have the following visualizations regarding cumulative Purchase Orders:

Due to there are no data for June in 2020, I need that the cumulative of June should be completed with the cumulative of the previous month. I'm using the following measure for "2020":

2020 = if(isblank([Monto YTD]),blank(),CALCULATE([Monto YTD] , DATESYTD(Calendario[Date])))

whrere:

* Monto YTD = SUM(PurchaseOrder[MontoUSD])

* Calendario = Calendar Table

Thanks in advance.

Joao

[Syndicate_Admin]

Power bi rolled over per month with blank months

Good afternoon

Please, could you help me, I have companies with data up to the month of October ( 10) , when segment in month 10 , only Development appears. How do I make culture appear with its last accumulated of June (6)

[amitchandak]

@JoaoMS , then why are using isblank, remove that and try

2020 = CALCULATE([Monto YTD] , DATESYTD(Calendario[Date]))

This will give data in June

[JoaoMS]

Hi, thank you for the quick response. Indeed, it works for June, but now, the following months (september to december) have the cumulative of August, and I want those months to be "blank". Is there a way to consider those months empty? Regards, Joao

[amitchandak]

for that put a filter like this and try

My data example

YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))

or

YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))

Commented is alternate option

Ideally, we should relative/advance filter , less than today

[JoaoMS]

Hi, I tried bothoptions but with the same previous results: * First formula with Today() resulted as using "2020 - CALCULATE([Monto YTD] , DATESYTD(Calendario[Date]))" * Second formula with MAXX resultad as using IF(Isblanck() ... Regards

[amitchandak · Accepted solution]

@JoaoMS , Try like

2020 = if(isblank([Monto YTD]) && max(Calendario[Date]) >today(),blank() ,CALCULATE([Monto YTD] , DATESYTD(Calendario[Date])))

[GreyWolf]

You Sir are my new hero. I have been seaching the forums and youtube channels for 5 days and tryed about 5 different measures and nothing fixed the line graph gap issue in a running sum or count for when a value in a row is blank. And then I found your solution which solved the problem. I would have thought that this is a relative common issue when wotking with cummulated values. I am super greatful to you Sir.

[JoaoMS]

Perfect, it works! Thank you

[Greg_Deckler]

@JoaoMS - Sorry, what is the question? 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.