cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker Skilled Sharer
Skilled Sharer

YTD up to month selected help

Hi Everyone,

 

I am having a major issue trying to figure this one out. I have a table with sales data (volume, $, and dates) I also have a seperate DateKey table. What I am trying to get is if a user selects one particular month in a filter that a measure will calculate the sum of sales for that month and all proceeding sales to the start of the year.

 

Everything I try keeps giving me the wrong stuff so hopefully you great people will have a simple answer.

 

Thanks,

 

Giles

5 REPLIES 5
Sean Community Champion
Community Champion

Re: YTD up to month selected help

@GilesWalker

Have you tried using TOTALYTD

Without any sample data...

YTD Measure =
TOTALYTD (
    SUMX ( TableName, TableName[Volumne] * TableName[Price] ),
    CalendarTable[Date]
)

Then if you have Year and Month slicers it should work! Smiley Happy

If no Year is selected it will default to current year up to selected month and if no month is selected up to date!

If Year is selected and no month it will give you total for the year and if a month is also selected YTD for the selected year!

GilesWalker Skilled Sharer
Skilled Sharer

Re: YTD up to month selected help

@Sean thanks for the response. I inputted the below DAX formula:

 

 YTD test = TOTALYTD(

SUMX ( Table1,  [Actual residual]  ),

DimDate[Date]

)

The measure [Actual Residual] is SUM (Table1[Dollars]) - basically it just sums the total revenue.

 

With your formula if I select a month the result is the same as [Actual Residual] it doesnt do a YTD total. If no months are selected your formula result is less than [Actual Residual]

 

Any thoughts?

 

Thanks,

 

Giles

 

Sean Community Champion
Community Champion

Re: YTD up to month selected help

Since you already have a Measure - How about this?
YTD test = TOTALYTD(
[Actual residual],
DimDate[Date]
)
GilesWalker Skilled Sharer
Skilled Sharer

Re: YTD up to month selected help

@Sean - That was the first measure I tried, the issue is if I pick a month in the filter the YTD figure and the [Actual Residual] measure are the same, the TOTALYTD doesn't appear to do what it should.

 

One thing I have noticed is the TOTALYTD figure when no month is selected but the year is selected, the totals are different. My understanding of TOTALYTD is that it takes into account outside filters, is that correct?

sanjusanjeet
Regular Visitor

Re: YTD up to month selected help

@Sean, Hey did you get it working? I also have the same situation. My MTD, YTD works flawlessely. However, if I select multiple months in filter say June,July,Aug for Year 2018; my YTD shows YTD till AUG, which should show SUM of MTD b/w June till Aug only.

 

If I select these months on row or columns axis, my YTD shows the bifurcation correctly. However same doesnt work if I get months as filter axis.

 

Any help?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors