cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Cumulative chart with empty data in some months

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

Graph.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@JoaoMS , Try like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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

@JoaoMS , Try like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted

Perfect, it works! Thank you

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors