Forum Discussion

ricardo_cvo's avatar
ricardo_cvo
Icon for Helper I rankHelper I
8 years ago

Dax Sales by Period

Good afternoon everyone, I am new to dax and need to get the sum of sales as follows: Sales from day 1 to day 30 Sales from the 31st to the 60th It's possible? Thank you
Need Help
Washivale's avatar
Washivale
Icon for Resolver V rankResolver V
8 years ago

HI Ricardo,

 

Check the following if it works:

 

Sales 1 to 30 Days = CALCULATE([Sum Sales], DATESBETWEEN(dCalendar[Date],MAX(fSales[Date of Sale])-60,MAX(fSales[Date of Sale])-30))

 

Sales 31 to 60 Days = CALCULATE([Sum Sales], DATESBETWEEN(dCalendar[Date],MAX(fSales[Date of Sale])-30,MAX(fSales[Date of Sale])))

 

Thanks,

Sandeep

ricardo_cvo's avatar
ricardo_cvo
Icon for Helper I rankHelper I
8 years ago

Thanks for the feedback,

 

I tested the Sales 31 to 60 Days formula that in the example is from 06/13 to 07/07 and is giving 123537,10 how much the value should be 118692.82.

 

Regards

  • Washivale's avatar
    Washivale
    Icon for Resolver V rankResolver V
    8 years ago

    Hi Ricardo,

     

    Please give it a try with below measures.

     

    Sales 31 to 60 Days =

    var ld = CALCULATE ( MAX ( dCalendar[Date] ), ALLSELECTED ( dCalendar[Date] ))
    var sd = ld-30
    return
    CALCULATE([Sum Sales], DATESBETWEEN(dCalendar[Date],sd,ld))

     

    Sales 1 to 30 Days =
    var ld = CALCULATE ( MAX ( dCalendar[Date] ), ALLSELECTED ( dCalendar[Date] ))
    var sd = ld-60
    var lp = sd + 30
    return
    CALCULATE([Sum Sales], DATESBETWEEN(dCalendar[Date],sd,lp))

     

    let me know if it works

    • ricardo_cvo's avatar
      ricardo_cvo
      Icon for Helper I rankHelper I
      8 years ago

      Hello,

       

      Thanks for returning, I tested the formulas sent, but the values disappear when the formula is applied.

       

      Regards

      • Washivale's avatar
        Washivale
        Icon for Resolver V rankResolver V
        8 years ago

        Hi Ricardo,

         

        When you say  01 to 30 days out of 60, should we be looking at 60 days prior from today's date , or 60 days prior to the last date on your table.

         

        Also, Do you have a date slicer on your report, last formula i sent will work on the dynamic date selections (if you could add a date slicer and select date from which backdated calculation should happen, it would return values.)

         

        Thanks

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

    Hi,

     

    Looks like a -1 or +1 is missing somewhere.  Try 61 instead of 60 or 31 instead of 30.