YTD, MTD, WTD Unable to calculate

[nerra]

Hello All!

I'm a bit stuck with calculating the YTD , MTD and WTD measures for Sales.

Currently my Calendar table is set up like this:

I have calulated columns for Fiscal Period/Year/Week  Start/End dates.

I've tried the following for Month To Date:

MTD Sales CY 2 =  CALCULATE([Total Net Sales] , FILTER(ALL(BusinessDate) , BusinessDate[Fiscal Period Start Date]>=BusinessDate[Fiscal Period Start Date] && BusinessDate[Business Date]<=MAX(BusinessDate[Business Date])))

 This returns cummulatie value, but the total is for all periods ever. 

MTD Sales CY 4 = CALCULATE([Total Net Sales],

FILTER(BusinessDate, BusinessDate[Business Date]>=BusinessDate[Fiscal Period Start Date] && BusinessDate[Business Date]<=MAX(BusinessDate[Business Date]))) 

returns the total for the entire month instead of month to date.

I'm circuling in loops and can't get out. 

[amitchandak]

For ytd and MTD , prefer date intelligence function datesmtd, totalmtd, datesytd and totalytd. ytd can take year and date

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

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('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"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[nerra · Accepted solution]

Actualy, that would not take into account Fiscal month/week/year.

Looks like

MTDSales CY = CALCULATE (

[Total Net Sales],

FILTER (

ALL( BusinessDate ),

BusinessDate[Fiscal Year]= MAX (BusinessDate[Fiscal Year])

&& BusinessDate[Fiscal Period] = MAX ( BusinessDate[Fiscal Period])

&& BusinessDate[Business Date] <= MAX ( BusinessDate[Business Date] )

))

is the solution. Thanks!