Forum Discussion

Alde's avatar
Alde
Frequent Visitor
4 years ago

Show last month without selecting filter

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...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
    Alde
    Frequent Visitor
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.