Hello commnuity
I want to compare the cumulative of one variable over 2 years.
When I use the TOTALYTD formula I get the data I want:
cumulativeImpressions = TOTALYTD(sum(database[Impressions]);database[finaldate].[Date])
However, now I'm importing the data with Direct Query and I'm using a formula to calculate it, The problem is that when I use the formula the cumulative continues in 2016 with the data in 2015
cumulativeImpressions =
CALCULATE (
SUM (database[impressions] );
FILTER(ALL(database);
database[finaldate] <= MAX(database[finaldate]))
)
I want to obtain with the formula the same result as with TOTALYTD function
Thank you very much for your help
Hi @ilana105,
How do you set the Axis level, you have year and month field in your source data, you select the month as axis level, the year as legend level, right? If it is, you’d better add filter in measure to cumulative sum for each year, rather than all data. The TOTALYTD Function evaluates the year-to-date value of the expression in the current context. So it return the total sum for each year.
I try to reproduce your scenario as follows.
Create month and year calculated columns.
Year = YEAR(Sales[DATE]) Year = YEAR(Sales[DATE])
Create measure using the formula below. The values function will return a table including one year.
cumulative = CALCULATE(SUM(Sales[SALE]),FILTER(ALL(Sales),Sales[DATE]<=MAX(Sales[DATE])),VALUES(Sales[Year]))
Create the line chart, you will get expected result same to using TOTALYTD function like the following screenshot.
TotalYTD = TOTALYTD(SUM(Sales[SALE]),Sales[DATE])
If you have any other issue, please feel free to ask.
Best Regards,
Angelia
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
