Calculate YTD and MTD based on Month name slicer

[Anonymous]

Hi Team,

i would like to calculate YTD and Current Month using a slicer which has "Month Name"

I was able to get Current month using the below formula=

To display current month =

var  MYSELECTION=SELECTEDVALUE('Month Sorting'[Month])

return

calculate(SUM(Details[Value]),FILTER(Details,Details[Actuals_]="Actuals"),'Date Table'[Month Name]=SELECTED1).

Now to calculate YTD im finding difficult, since the slicer has month name

YTD =

var abc=SELECTEDVALUE('Date Table'[Month Name])

var maxdate=EOMONTH(abc)

var Minvalue=Min('Date Table'[Date])

return

TOTALYTD(calculate(SUM(Details[Value]),FILTER(Details,Details[Actuals_]="Actuals"),

Details[Dates__]=Minvalue&& Details[Dates__]

Im getting error for YTD. can you please help.

Regards,

Husna

[Greg_Deckler]

YTD =

var abc=SELECTEDVALUE('Date Table'[Month Name])

var maxdate=EOMONTH(abc)

var Minvalue=Min('Date Table'[Date])

return

 SUMX(ALL('Details'),Details[Dates__]>=Minvalue && Details[Dates__] <= maxdate)

[Anonymous]

Hi @Greg_Deckler 

var abc=SELECTEDVALUE('Date Table'[Month Name])

var maxdate=EOMONTH(abc)

Here abc contains Month names which are present in slicer

however i do not have an idea to get date from month name "Maxdate=EOMONTH(abc)" is not correct

Can you please help me on this

[Greg_Deckler]

Ah, you'll need to translate it. You are also going to need your year, no idea about your data for that.

So, you could add something like this:

VAR __MonthNum = SWITCH(SELECTEDVALUE('Date Table'[Month Name]),

  "January",1,

  "February",2,

  "March",3,

  ...

  )  //end SWITCH

VAR maxdate=EOMONTH(DATE(YEAR(TODAY()), __MonthNum,1),0) //current year, month number first of month, get the end of month

..

[Anonymous]

Hi @Greg_Deckler 

I tried using the below measure:

YTD_Measure1 =

var Max1= SWITCH(SELECTEDVALUE('Date Table'[Month Name]),"January",1,

"February",2,

"March",3,

"April",4,

"May",5,

"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,12)

Var Maxvalue=EOMONTH(DATE(YEAR(TODAY()),Max1,1),0)

Var Minvalue=Min('Date Table'[Date])

return

TOTALYTD(calculate(sum(Details[Value])),filter(Details,Details[Actuals_]="Actuals"),Details[Dates__]>=Minvalue && Details[Dates__]<=Maxvalue).

Now i get error "Column 'RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61' in table 'Period Table' cannot be found or may not be used in this expression"

where details dates im getting from period table using related function.what should i do to resolve this?

[amitchandak]

@Anonymous , Does your month name have Year. Then in that case if you have selected a month name from date table, the time intelligence should have worked

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

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

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

If the month name do not have year we have to force a year