add YTD and MTD to sales figures

[chakrabmonoj]

I am trying to compare sales for two separate years on ytd and mtd basis. But the first month in my data is jul and not jan; so for YTD current year it sould add jul-dec and similarly for the previous year. For MTD it should compare whatever is the latest month for the current year.

I have tried using datesinperiod with firstdate and also datesytd - but its not working :

My data looks like this :

[tringuyenminh92]

Hi @chakrabmonoj,

Could you click see details to share error message to me and did you try with date column instead of month column inside datesytd method?

[chakrabmonoj]

Hey,

The screenshot of the error message :

When I am supplying the field for the firstdate function, its asking me to choose one of the following arguments (but the help on syntax does not show these arguments) :

So since I am summing the values for months, i thought I would choose "month" from these arguments.

The added problem is my year is not in standard calendar as my period does not start from jan, but starts from Jul - hence not sure how do I define period starting Jul till the lastmonth in the column

[tringuyenminh92]

Hi @chakrabmonoj,

As i know, the second parameter is optional for end date, you could ignore it and use : datesytd(table1[Date]). And as my understand, you are trying adjust the start date to Jul instead of Jan as default? So we need figure out another formula. Did you try with DATESINPERIOD or DATESBETWEEN  and TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date]? 
Could I have your small sample file? So I could quickly try some ideas and let you know my solution.

[chakrabmonoj]

Your solution works after I changed "month" to "date" in the sense that the data is being compiled without error. Problem is I dont know whether datesytd, or datesinperiod in combination with firstdate and lastdate, can work since I am working with a non-standard calendar

[tringuyenminh92]

Hi @chakrabmonoj,

Your YTD is from Jul to end of that year? or from Jul to Jul of next year? Is this topic same as your expectation? https://www.powerpivotpro.com/2010/07/use-time-intelligence-functions-to-do-a-running-sum-of-the-las...  (last 6 months of year)

[chakrabmonoj]

our financial year is jul-jun - this means half of the year will fall in one calendar year and the 2nd half in the next.

How can i send you the data file? 

[tringuyenminh92]

Hi @chakrabmonoj,

You could try with TOTALYTD for fiscal year:

YTD = TOTALYTD(sum(table1[VALUE]),table1[DATE],"7/31")

[chakrabmonoj]

by the  way - is the "7/31", starting date in your formula or the end date? in which case, my problem still remains unresolved. My problem is that I need to sum up values between jul-dec for 2015 and 2016, but preferably, both start and end periods supplied dynamically

[chakrabmonoj]

Thanks - but is it possible to make the "7/31" dynamic itself; so that this one is always picked up a {firstdate} or {lastdate}?

what does "7/31" mean? 31st jul, right?

by the way - is there a function to only address the month or year part of a date field?

[tringuyenminh92]

Hi @chakrabmonoj,

it's too much questions, so let me go one by one:

• dynamic for "7/31": will be updated after observed your sample file
• "7/31" meaning: it's end date of each period
• With date column, you could Dates[Date].[Year] or Dates[Date].[MonthNo], if it not showing in your expression, you could use year() and month() method
• Are you expectation sum period is from Jun-dec or Jul to Jun? it's confusing now.

[chakrabmonoj]

YTD will be jul-dec 2016 and PYTD will be jul-dec 2015. But the start and end month preferably should be dynamic

by the way - did you get my file?

[tringuyenminh92]

Hi @chakrabmonoj,

I'm not good at some built-in function like TOTALYTD and filters dates func like datesbetween or datesinperiod methods, so let me show you my approach for your dynamic start-end YTD as Time-patterm topic:

• Create Dates table: Dates = CALENDAR(date(2014,1,1),date(2020,1,1)) (can adjust more)
• Making relationship between your fact table and dates 

• Create YTD calculated measure:

YTD = CALCULATE(sum(table1[VALUE]), 
                 FILTER(ALL(Dates),
					SUM(table1[VALUE])>0 && Dates[Date]<=MAX(Dates[Date]) && Dates[Date].[Year]=year(max(Dates[Date]))   //this is for cummulative as time-pattern
					&& Dates[Date]   >= date(Dates[Date].[Year],FIRSTNONBLANK(Configuration[month],1),FIRSTNONBLANK(Configuration[day],1) )   // this is to limit start date
 && max(Dates[Date]) <= 	date(Dates[Date].[Year],LASTNONBLANK(Configuration[month],1),LASTNONBLANK(Configuration[day],1) ) //this is to limit end date
					 ) )

This is my configuration table (for dynamic start/end date)

I have sent you the sample file, please check your inbox and let me know if there is any concern. I will be back in next hours for my dinner ^_^ 

[chakrabmonoj]

this seems to be working. but just so that I can understand the logic properly :

1. what is the table called "configuration" doing - could not understand the month{7 & 9} and day{1,31}; why?

2. How are you getting to sum between jul and dec when the formula seems to be using MAX everywhere - will that not return "dec" as the month, since that is the maximum value in the dates[date] field?

3. how is the formula also getting the same period for 2015 as well, since seeking MAX....from dates[date].year, would return 2020, as that is the max value right?

but thanks for the solution - really appreciate.

[tringuyenminh92]

Hi @chakrabmonoj,

1. That's table created by Enter Data, for your case, you could input in excel or somewhere and use it as your dynamic parameters. That's sample purpose

2&3. This is really complicated thing cause it relates filter context in DAX. But you are thinking about row context (row by row). So could you please refer this topic as my explanation (this is fully topic about time-patterm and filter context articles to understand why we could get "current date" by max in that filter context of measure)

[chakrabmonoj]

Nice article - is there a way to query the data table, by quickly typing the formula max etc, and see what value it returns for the function/formula.

E.g. is it possible to type max(dates[date]), somewhere, to check what value it returns? that way I could debug your formula quickly and understand the logic so that I can use similar logic elsewhere.

couple of more doubts :

1. Why is table1[date] *->1 with the dates[date] - why do we need a many-to-one relating ship between these two dates? 

2. When I refere to the configuration - the source shows two values for [month] - 7 and 12 (which is jul and dec), but when I am seeing the same table in the relations or table view, I see values of 7 and 9 for the same (which is jul and sep), right? why are you replacing the values?

3. I am still not clear, how are you extracting both 2015 and 2016 from dates[date], since none of them is the MAX value in this column

thanks for the help - really appreciate. Sorry for bugging as I am quite new to BI

[tringuyenminh92]

Hi @chakrabmonoj,

1. not sure, the best thing is understanding filter context and row context from that article. Then you could imagine the value in that context. 

Your table1 is fact table or transaction table so there multiple value of date column. Dates table is Dimension table/master table - it's unique value with date column so we have many to one relationship

2. I did to show you that when value of configuration table changing, the measure will be reflected.(cause you need dynamic variable of start/end date). 

3. It's context, in that filter context, you will have 2015 or 2016 or another years. Filter context: https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

it's fine to me with your concerns, i like that, it helps me gain more lessions. 

[chakrabmonoj]

Hey,

I am kind of getting a hang of your formula - but still lost as to how this formula is (which part of it is designed) to return the values for the year 2015 and 2016...which part of the formula is designed to filter/extract that from dates[data]?

Is it possible to do keep YTD (for 2016) and PYTD (for 2015) for months{jul-dec} as two separate measures so that I can chart them by category side by side and with a line graph showing the change? [ref. the page called "L3L6"]

thanks

[tringuyenminh92]

Hi @chakrabmonoj,

Please try with:

Prev YTD = CALCULATE(sum(table1[VALUE]), 
                 FILTER(ALL(Dates), SUM(table1[VALUE])>0 &&
					 Dates[Date] <= MAX(Dates[PY Date]) && Dates[Year]=MAX(Dates[Year])-1
 			&& Dates[Date].[MonthNo] >= FIRSTNONBLANK(Configuration[Month],1)
 
					 ) )

YTD = CALCULATE(sum(table1[VALUE]), 
                 FILTER(ALL(Dates),
					SUM(table1[VALUE])>0 && Dates[Date]<=MAX(Dates[Date])
					&& Dates[Date].[Year]=YEAR(MAX(Dates[Date]))
&& [Prev YTD]>0					
 
					 ) )

I have sent you updated file for reference

[diegorumo]

Hi, could you please send me the file for reference? thanks a lot!

[diegorumo]

Hi, could you please send me the file for reference? thanks a lot!