Show last month without selecting filter

[Alde]

Hello Everyone,

I'm a beginner and  struggling to formulate a measure to show a specific month(or last month) in Year-to-date card when I do not select any month on filter bar. I have columns, Month(a text column), YTD_OB_Actual, YTD_OB_Budget, YTD_CB_Actual and YTD_CB_Budget.

Please help me through this, I've spent hours finding the solution.

[amitchandak]

@Alde , End your calendar on today and use the time intelligence function. Or create based on today

QTD Today=
var _month = mod(month(Today()),3)
var _min = date(year(today()),month(Today()) -1* if(_month=0,3,_month) ,1)
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

LQTD Today=
var _month = mod(month(Today()),3)
var _min = eomonth(date(year(today()),month(Today()) -1* if(_month=0,3,_month) ,1),-4)+1
var _day = date(year(today()), month(today()) -3 , day(today()))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

YTD Today=
var _min = date(year(today()),1,1)
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max))

MTD=
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

LMTD =
var _min = eomonth(today(),-2)+1
var _max1 = today()
var _max = date(year(_max1),month(_max1)-1, day(_max1))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

LYMTD =
var _min = eomonth(today(),-12)+1
var _max1 = today()
var _max = date(year(_max1)-1,month(_max1), day(_max1))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

If calendar end of today of month end 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)))

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"))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs?t=200

[Alde]

Hi @amitchandak  

Thanks for this solution,

but the thing is I'm using the Month column of text, not actually from the calendar. 

Can I know how to relate this text Month column to the clender if I have to use month column from the calendar.

[Anonymous]

Hi @Alde ,

I think you can add Month column in calendar table and then relate the Table[Month] to Calendar[Month].

You can try to create a calendar table by code as below. [MonthShortName] is text format as "Jan" and [MonthName] is text format as January.

Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "MonthNum", MONTH ( [Date] ),
    "MonthShortName", FORMAT ( [Date], "MMM" ),
    "MonthName", FORMAT ( [Date], "MMMM" )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.