topic Re: Data accumulated over several years in Desktop

Data accumulated over several years

Syndicate_Admin — Fri, 22 Oct 2021 09:00:39 GMT

Good morning

I have the following problem:

I have created this graph of Orders accumulated over several years using this measure:

Accumulated orders = CALCULATE(SUM('Total Order Entry'[Amount]),DATESYTD('Master Calendar'[Day]))

What I need is that in the year 2021, when it reaches today, instead of the flat line continuing (because there is no data yet), that the line is cut there and does not continue.

It's possible?. Thank you

Jose

Re: Data accumulated over several years

amitchandak — Fri, 22 Oct 2021 08:48:49 GMT

@Syndicate_Admin , Create a YTD measure and take the month axis and year on Legend

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Re: Data accumulated over several years

Syndicate_Admin — Mon, 25 Oct 2021 14:06:49 GMT

Good morning

I'm afraid the solution doesn't work, it's the same:

What I would need is for future dates not to appear. For example, in the table below the graph, the months of November and December 2021 should come out empty because we have not yet arrived.

Thank you for your help

Re: Data accumulated over several years

Anonymous — Wed, 27 Oct 2021 02:30:12 GMT

HI @Anonymous,

According to your description, it sounds like you want to show the line graph continue even if there are no match records with the next time periods? If that is the case, I think the time intelligence function may not suitable for your requirement.
For this scenario, you can use the date function to manually define the filter ranges and change the compare operator to 'less than and equal to' current date(<=) then the line graph will be shown on the following time period even if no records existed.

Accumulated orders = VAR currDate = MAX ( 'Master Calendar'[Date] ) RETURN CALCULATE ( SUM ( 'Total Order Entry'[Amount] ), FILTER ( ALLSELECTED ( 'Total Order Entry' ), YEAR ( [Date] ) = YEAR ( currDate ) && [Date] <= currDate ) )

Regards,

Xiaoxin Sheng

Re: Data accumulated over several years

Syndicate_Admin — Fri, 29 Oct 2021 09:37:31 GMT

Sorry, it doesn't work, it gives error and nothing comes out.

Re: Data accumulated over several years

Syndicate_Admin — Tue, 02 Nov 2021 17:27:13 GMT

Hello, thanks for the help but it does not fix the problem.

I have used your measurement:

Accumulated orders =

WHERE currDate =

MAX ( 'Master Calendar'[Day] )

RETURN

CALCULATE (

SUM ( 'Total Order Entry'[Amount] ),

FILTER (

ALLSELECTED ( 'Total Entry Orders' ),

YEAR ( 'Total Entry Orders' [Date Entry (ERDAT)] ) = YEAR ( currDate )

&& 'Total Entry Orders' [Date Entry (ERDAT)] <= currDate

)

But the result is the same:

And what I was looking for is that, since there is still no data from 12/2021, that data should be 0 or empty instead of repeating the same data that there is in 11/2021

Re: Data accumulated over several years

Anonymous — Thu, 04 Nov 2021 06:43:41 GMT

Hi @Anonymous,

Perhaps you can try to add an if statement to check the current axis date and max available fact table date and use it as a filter to prevent the calculation on not existing date ranges:

Accumulated orders = VAR currDate = MAX ( 'Master Calendar'[Day] ) VAR maxDate = CALCULATE ( MAX ( 'Total Entry Orders'[Date Entry (ERDAT)] ), FILTER ( ALLSELECTED ( 'Total Entry Orders' ), YEAR ( 'Total Entry Orders'[Date Entry (ERDAT)] ) = YEAR ( currDate ) ) ) RETURN IF ( currDate <= maxDate, CALCULATE ( SUM ( 'Total Order Entry'[Amount] ), FILTER ( ALLSELECTED ( 'Total Entry Orders' ), YEAR ( 'Total Entry Orders'[Date Entry (ERDAT)] ) = YEAR ( currDate ) && 'Total Entry Orders'[Date Entry (ERDAT)] <= currDate ) ) )

Regards,
Xiaoxin Sheng

Re: Data accumulated over several years

Syndicate_Admin — Mon, 22 Nov 2021 17:12:05 GMT

Dear Sir,

I tried the formula, but it gives an error of: "The end of the entry has been reached":

Greetings

Jose

Re: Data accumulated over several years

Anonymous — Wed, 24 Nov 2021 07:29:09 GMT

Hi @josefmc08,

This issue seems more related to the brackets.

After check the snapshot, I found you add three more ')' at the end of the formula so that power bi cannot recognize and process this expression.

Regards,
Xiaoxin Sheng