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(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.

 

Need Help
Tips and Tricks

3 Replies

  • 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.