YTD and MTD based on Year and month Filter

[unnijoy]

I have a sales data for the last 3 years. In my dashboard i have month and year slicer. I placed two cards one for YTD and the other for MTD. I need to show the MTD and YTD slaes based on the below criteria.

1 If all Years are selected then it shoud show Latest year YTD. Same for for MTD

2 If multiple years are selected then YTD should show the total sum based on the year's selected. 

 Same principl for MTD Card also. If all years are slected then it should show the sum of all month slaes in the latest year. And if multiple months are selected then it shoul show the sum of sales based on the month's selected.

I have creaetd a clander table by using the below equation. and i am using this for year and month. Please help me to create a formula based on the below calender table.   

Clander table =ADDCOLUMNS(

CALENDAR(MIN(Errors[Month_YR]),MAX(Errors[Month_YR])),

"Year",YEAR([Date]),

"Monthn",MONTH([Date]),

"Month",FORMAT([Date],"mmmm"),

"Month Year",FORMAT([Date],"mmm-yy"),

"MonthYearSort",FORMAT([Date],"yyyymm")

)

[PC2790]

Your second requirement can be fulfilled using the below measure:

Selection =

var Selectedvalue = if (HASONEVALUE(DatesTable[Year]),VALUES(DatesTable[Year]))

return

CALCULATE(sum(Sales[Sales Amount]),FILTER(Sales,SEARCH(Selectedvalue,Sales[YearValue],1,Blank())))

You can further apply if condition tofulfil your 1st condition

[amitchandak · Accepted solution]

@unnijoy , Based on Intital description. I think You need use has one value.

if it has one value then use time intelliegnece else use simple sum measure

example Ti measure

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

for hasonevalue refer : https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[unnijoy]

Hi @amitchandak ,

When i use the month from the calander table that we are using it is not giving me the correct data.

Clander table =ADDCOLUMNS(

CALENDAR(MIN(Errors[Month_YR]),MAX(Errors[Month_YR])),

"Year",YEAR([Date]),

"Monthn",MONTH([Date]),

"Month",FORMAT([Date],"mmmm"),

"Month Year",FORMAT([Date],"mmm-yy"),

"MonthYearSort",FORMAT([Date],"yyyymm")

)

"Month Year" is used for month selection. 

How can i use the "YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))" in this case.

When i try to use it is givig me error saying Month year is in text format.

[unnijoy]

Hi @amitchandak ,

thanks for your quick help. I tried using the above formula. But when i select multiple month MTD is showing the sum of sals for the latest month in the selected month. For example, if i select Oct -20,Nov -20 & Dec -20. Currently it is showing the Dec-20 sales sum as MTD. Actulay it should show the sales sum of Oct -20+Nov-20+Dec -20. And of all months are select then it should show the latest month sales.