MTD Totals

[Razorbx13]

Hello!  I have a P&L with MTD, YTD and PYTD totals.  I filter the table by a Month End date to get the current month end and then all month ends prior to that.  As an example, I have a user select a filter of MONTH END is on or before 9/30/2020.  Well the net totals of the calculation below (1-9) are correct for each of the categories, but I am bringing in data from months other than 9/30/2020.  I can filter for MONTH END "IS" 9/30/2020 and everything is fine, but my YTD and PYTD are now off.  So is there a way to like put a command in here to say filter for the maximum month end date in my returned records instead of all months?  In this case the Max date is 9/30/2020.  The max date will always be a month end of what the user filters on.

Thanks in advance.

MTD Actual = CALCULATE (    

    TOTALMTD (

    SWITCH ( [Row Selected Calc (SO)],

1,[Revenue (SO)],

2,[Cost of Sales (SO)],

3,[Gross Margin (SO)],

4,[Salary/Benefits (SO)],

5,[Other Expense (SO)],

6,[Net Income Before (SO)],

7,[Direct Allocations (SO)],

8,[Net Income After (SO)],

9,[Capital Expenditures (SO)] )

    ,'Time (GL Month End)'[Calendar Date]

    )   

     ,FILTER('GL Transactions (Statement of Operations)',[Account Type] = "ACTUAL")

    )

[Anonymous]

Hi @Razorbx13 

Please do yourself a big favour. Read this Time Intelligence in Power BI Desktop - SQLBI. Do not try to overthink problems that have already been solved a long time ago.

[amitchandak]

@Razorbx13 , Please use time intelligence for the same  with date tbale

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

or

YTD QTY forced=
var _max = maxx('order',[Order date])
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

OR

YTD QTY forced=
var _max = maxx('Date',[date])
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max1 =maxx('Date',[date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Razorbx13]

And I guess another question.  This month is April 2021.  The user filters to 9/30/2020.  Does the formula work on that filter, or is the context April 2021?

[Razorbx13]

Can that dates table be one I created in sql server and imported into my model from he data warehouse?  I do not have an internally built date table using PowerBI.  Use this other dates table all over he place as it has about 60 formatted fields with holidays, weekends, days, quarters, months, years and other fields for various calendars already. Our DBAs maintain it.