Forum Discussion

Kaladin's avatar
Kaladin
Frequent Visitor
3 years ago
Solved

Dax date/time filtering

I am trying to do last 1,2 and 3 months dax filter to count a column occuring in the those months. I am having trouble with have intelligent time convert the year to last year if we are looking at november 2022, december 2022, and january 2023 if the current month is february. 

 

currently using this just to get last months data. 

 

CALCULATE(SUM(Time[Hrs]),FILTER('Time','Time'[date_entered_utc].[Month] == [Last Month Month Name] && 'Time'[date_entered_utc].[year] == YEAR(TODAY()) ))
 
Last Month Month Name = SWITCH(MONTH(TODAY() ) - 1, 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "Unknown month number")
 
thanks for any direction. 
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Kaladin , use date table and time intelligence

     

    example

    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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[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)))

    2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

     

    Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

    2 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Kaladin , use date table and time intelligence

     

    example

    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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[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)))

    2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

     

    Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

    2 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Kaladin 

    try like:

    Last3MonthTotal =
    CALCULATE(
         SUM(Time[Hrs]),
         FILTER(
            'Time',
            'Time'[date_entered_utc]=<EOMONTH(TODAY(),-1)
                  && 'Time'[date_entered_utc]>=EOMONTH(TODAY(),-4)+1
        )
    )