cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Data accumulated over several years

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

8 REPLIES 8
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Dear Sir,

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

Greetings

Jose

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Super User

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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors