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

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Share some data, explain the question and show the expected result.