cancel
Showing results for
Did you mean:
Highlighted Helper II

## Cumulative chart with empty data in some months

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

Joao

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User IV

@JoaoMS , Try like

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

Proud to be a Super User!

7 REPLIES 7
Highlighted Super User IV

@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

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted Super User IV

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

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

This will give data in June

Proud to be a Super User!

Highlighted Helper II
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
Highlighted Super User IV

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

Proud to be a Super User!

Highlighted Helper II
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
Highlighted Super User IV

@JoaoMS , Try like

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

Proud to be a Super User!

Highlighted Helper II

Perfect, it works! Thank you  