Data accumulated over several years

[Syndicate_Admin]

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

[v-shex-msft]

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

[Syndicate_Admin]

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

[v-shex-msft]

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

[Syndicate_Admin]

Dear Sir,

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

Greetings

Jose

[v-shex-msft]

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

[Syndicate_Admin]

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

[amitchandak]

@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"))

[Syndicate_Admin]

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