cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Cumulative - empty for months with no data

Hello,

 

I'm trying to get a cumulative overview of the last 3 years using the following measure:

 

Voyages_agg_test = CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); AND( 'Calendar'[Date] <= MAX(CE[COD]) ; YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) )) ))
 
 

Working well except for the month where I have no voyage, i.e., September. The information from 2017 is missing. 

Voyages.PNG
 
Any idea what can be causing this?
 
Thank you,
João
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Cumulative - empty for months with no data

@JoaoPvP , If you are using Year = max( year) Than is as good YTD ??

Voyages_agg_test = CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); AND( 'Calendar'[Date] <= MAX(CE[COD]) ; YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) )) ))

 

Try like

Voyages_agg_test = CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); AND( 'Calendar'[Date] <= MAX('Calendar'[Date] ) ; YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) )) )

 

because when COD is not there it will fail

you can have filter to stop at max cod value

 

like

Voyages_agg_test =
var _max = MAXX(allselected(CE),CE[COD])
return
CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); 'Calendar'[Date] <= MAX('Calendar'[Date] ) && YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) &&
'Calendar'[Date] <= _max) ) )

 

For YTD refer : YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a



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

4 REPLIES 4
Highlighted
Memorable Member
Memorable Member

Re: Cumulative - empty for months with no data

What is the desired behaviour? It's not clear from your chart what is wrong..

Highlighted
Frequent Visitor

Re: Cumulative - empty for months with no data

Hi Andre,

 

I would like to have a bar in September 2017, which is missing from the chart.

 

Thank you,

João

Highlighted
Super User IX
Super User IX

Re: Cumulative - empty for months with no data

@JoaoPvP , If you are using Year = max( year) Than is as good YTD ??

Voyages_agg_test = CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); AND( 'Calendar'[Date] <= MAX(CE[COD]) ; YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) )) ))

 

Try like

Voyages_agg_test = CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); AND( 'Calendar'[Date] <= MAX('Calendar'[Date] ) ; YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) )) )

 

because when COD is not there it will fail

you can have filter to stop at max cod value

 

like

Voyages_agg_test =
var _max = MAXX(allselected(CE),CE[COD])
return
CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); 'Calendar'[Date] <= MAX('Calendar'[Date] ) && YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) &&
'Calendar'[Date] <= _max) ) )

 

For YTD refer : YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a



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
Frequent Visitor

Re: Cumulative - empty for months with no data

Hi,

 

Used 

Voyages_agg_test = CALCULATE(count(CE[Voyage]);DATESYTD('Calendar'[Date]))
 
Had no idea that Datesytd existed. Marking your reply has right since the documentation you are mentioning has a path to this.
 
Thank you.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors