Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

MTD and Last Year MTD - DAX - DirectQuery

I am using this formula to calculate MTD transaction counts for prior year:   PY Trx = CALCULATE(sum(Daily[Trx]), FILTER( Daily, Daily[Year]=YEAR(max(Daily[Date]))-1 && Daily[Month]=MONTH(MAX(Dail...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    I noticed there are some problems with grouping in PowerBI. Now it is working without any changes in my previous calculations.

     

    Thank you for your time and help! 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

 

Create a Calendar Table and build a relationship from the Date column of the Daily Table to the Date column of the Calendar Table.  Create a slicer from the Date column of the Calendar table and select any date there.  Write these measures

 

Sales MTD = CALCULATE(sum(Daily[Trx]),MTD(Calendar[Date]))

Sales MTD LY = CALCULATE([Sales MTD],SAMEPERIODLASTYEAR(Calendar[Date]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Ashish,

     

    Thank you for your response!

     

    I need to align current year weekdays with those of last year, not sure how I can achieve this by using SAMEPERIODLASTYEAR formula. 

    Also, this works when I manually filter dates, however this table is getting updated on daily basis. Any recommendation on any alternative approach?

     

    Much obliged! 

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

       

      I can help you only if you share some data and the expected result.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I noticed there are some problems with grouping in PowerBI. Now it is working without any changes in my previous calculations.

         

        Thank you for your time and help!