Month over month aggregated value is blank for months with no data

[OliverFl]

Hello everybody,

I have been working with a report which worked great until we had a few months with no data. After searching around the forums, googling, and asking a few chatbots without resolve, I figured it is time to create my first forum post.

I have a table which shows opened tickets, closed tickets, net opened tickets and Open tickets aggregated.

Everything works as expected for months with data, but for months with no data, the aggregated measure turns blank.

The measure is calculated like:
Tickets Open Agg =
CALCULATE (
   SUMX (

          VALUES ( 'Date Table'[Date] ),

          [Net Open tickets] + 0

              ),
   FILTER (
      ALL ( 'Date Table'[Date] ),
      ISONORAFTER ( 'Date Table'[Date], MAX ( 'Date Table'[Date] ), DESC )
   )
)

As you can see I have a proper date table with all the dates necessary. My problem seems to be that when I have no data, the Tickets open agg-measure simply isn't calculated, and I'm unable to force it to calculate.

If you need anymore detail, please let me know.

Many thanks in advance!

[v-zhouwen-msft · Accepted solution]

Hi MFelix ,thanks for the quick reply, I'll add more.

Hi @OliverFl ,

Try this 

Measure = 
CALCULATE(SUMX('Table',[Net_open_ticket]),FILTER(ALL(DateTable),ISONORAFTER([Date],MAX('DateTable'[Date]),DESC))
)

Final output

Best Regards,
Wenbin Zhou

[MFelix]

Hi @OliverFl ,

Try the following code:

Tickets Open Agg = SUMX(
			ADDCOLUMNS(
				SUMMARIZE(
					'Date Table',
					'Date Table'[year],
					'Date Table'[month]
				),
				"_NetOpenTickets",  ,CALCULATE(
					[Net Open tickets] + 0,
					FILTER(
						ALL('Date Table'[Date]),
						ISONORAFTER(
							'Date Table'[Date],
							MAX('Date Table'[Date]),
							DESC
						)
					)
				)
			),
			[_NetOpenTickets]
		)

[OliverFl]

Hi, thanks for the suggestion!

I'm afraid I still get the same problem, all months with no data are blanks but the aggregation measure works just fine.

I think there is a comma too much, right? "_NetOpenTickets", ,CALCULATE("

[MFelix]

Yes you are correct about the comma sorry for teh error copying