Need to calculate ytd running total to calculate the running total till the max month of the data

[hemantsingh]

Hi there,

  I am trying to write a measure which can calculate the running totals across months of various years. I am somehow getting accurate result with my formula but the only problem with my formula is that it is calculating or rather copy pasting the same amount in the months that are not present in my data.

  My data has sales till 31-01-2016 but somehow the running total formula which i wrote is calculating the running total for feb ,march & so on till december but copy pasting the january sales total in all months.

here is what i tried.

Runningtotal_YTD = calculate(sale,datesytd(datekey(date)))

I also tried 

runningtotal_YTD= Totalytd(sales,datekey(date)).

Both formula are fetching the identical results. 

How can i resolve it??

[sdjensen · Accepted solution]

@hemantsingh you could try something like this

Runningtotal_YTD = 
IF ( MIN( datekey(date) <= MAX(facttable[datecolumn]);
calculate(sale,datesytd(datekey(date)))
)

[hemantsingh]

thanx @sdjensen: This way around worked more than perfectly. 

Regards

[MattAllington]

The easiest way to solve the problem is to make sure your calendar table doesn't extend past your data. I wrote a blog about it here. 

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

[hemantsingh]

Hi @MattAllington

 My sincere thanks for reverting to my both queries. I read your blog & it is really helpful but it is a solution in power query whereas my Problem is that i have already created a date table within DAX & has made numerous calculated column too under it. Can you suggest some way to dynamically control end date in within Power BI using DAX??