Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum between two dates

Hi,

 

In one of my reports I use TOTALMTD to give the monthly values of that month. However I need a daxformula that summarize a value between two dates (will be a static number throughout the report.) but I can't seem to get it to work.

 

I want to calculate the number of bus salesmen between the dates 2019/02/01-2019/02/28.

 

I have created a calendar table as well that I use for my TOTALMTD. Does anyone know why it does not work?

 
 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi @Oscar1133 ,

    Here are 2 measures that can satisfy your need:

    Test bus salesmen=CALCULATE(SUM(ddd[RT Bus Salesmen]),DATESBETWEEN('Calendar table'[Date],DATE(2019,02,01),DATE(2019,02,28)))

    O

    Test bus salesmen=CALCULATE(SUM(ddd[RT Bus Salesmen]),FILTER(ALLSELECTED('Calendar table'),'Calendar table'[Date]>=DATE(2019,02,01) && 'Calendar table'[Date]<=DATE(2019,02,28)))

    Saludos
    Kelly
    Did I answer your question? Mark my position as a solution!

5 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi @Oscar1133 ,

    Here are 2 measures that can satisfy your need:

    Test bus salesmen=CALCULATE(SUM(ddd[RT Bus Salesmen]),DATESBETWEEN('Calendar table'[Date],DATE(2019,02,01),DATE(2019,02,28)))

    O

    Test bus salesmen=CALCULATE(SUM(ddd[RT Bus Salesmen]),FILTER(ALLSELECTED('Calendar table'),'Calendar table'[Date]>=DATE(2019,02,01) && 'Calendar table'[Date]<=DATE(2019,02,28)))

    Saludos
    Kelly
    Did I answer your question? Mark my position as a solution!

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      Hi, I'm starting with Powr BI and I'm getting to know the DAX features.

      I'd like to know if there's a way to do the following with DAX.

      I have a table with the following data:

      TeamOriginDestinationDeparture DateArrival DateDistance kmsTotal Distance
      T1Planta1Almacen101/03/2021 02:0101/03/2021 10:01150292
      T1Almacen1Planta101/03/2021 11:1001/03/2021 19:01142
      T1Planta1Almacen202/03/2021 03:0102/03/2021 12:01210420
      T1Almacen2Planta102/03/2021 13:1002/03/2021 22:01210
      T1Planta1Bodega103/03/2021 02:0103/03/2021 02:317299
      T1Bodega1Almacen103/03/2021 03:0103/03/2021 10:01150
      T1Almacen1Planta103/03/2021 11:1003/03/2021 19:01142

      Each row represents a section of the route and its distance, I need to be able to add the distance of the different sections and put it in a total, this in excel I do with formulas, but with DAX I can not find the way.

      Thank you for your attention, greetings...

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi  Syndicate_Admin ,

         

        Better create a new thread,then we will help to follow it.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!

  • az38's avatar
    az38
    Community Champion
    6 years ago

    hi Anonymous 

    try without filter and use more appropriate date statement, like

     

    = CALCULATE( SUM(ddd[RT Bus Salesmen]); DATESBETWEEN(ddd[Date];DATE(2019;2;1); DATE(2019;2;28)) )