Forum Discussion

moezsajwani's avatar
moezsajwani
Frequent Visitor
7 years ago

Dateadd for incomplete months

Hi, 

 

I have a sales table with dates and sale amount. Sale table is connected to Date Table which is used as a slicer in report. My report is based on months (coming from date table). I have calculated sales for last month using DATEADD -1, Month. However for current month i.e. October DATEADD returns me sale for complete September. What I want is same period last month so if my actual data in sales table is until 10th Oct, I want that function to return sales for 1 - 10th Sep instead of complete September. I can use daily interval in DATEADD but not all months have same days, so If I do -30, DAYS, the result for October 31 would be wrong.

 

Is there anyway around please? I am hoping this is a very common problem for many people.

 

M

Need Help
Tutorial Requests

7 Replies

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

    Hi moezsajwani,

     

    The problem is not within the DATEADD because it gets the corressponding value from the month, your issue is with the date column itself.

     

    When having time intelligence it will get the last day of the month as a maximum value so you are getting the full month if you change your slicer to one day earlier it will give only the 10 days of october.

     

    To work around this you should add your date to the visual filter or report filter and the select the option Relative date filtering and select is in the last X days, months or years.

     

    Should work as requested.

     

    Regards,

    MFelix

    • baravo's avatar
      baravo
      Icon for Helper I rankHelper I
      6 years ago

      Hi MFelix ,

       

      I am glad to find your explanation, however, the work around you suggested does not work for me.

      I set the relative date filtering in my report and the last month measure with DATEADD is still taking the whole month compared to the 10 days of this month.

       

      Any idea on how to solve this?

      Thank you very much.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi baravo ,

         

        I need a little bit more information but to what I can understand you want to compare MTD values with current month values until the current date or the selected date so if we are no the 20th it will be the first 20 days?

         

        You can use a TOTALMTD to do your calculation on the PBIX file attach you have data for 2 months (June and July).

         

        Data for June is always 1 and for July always 2 as you can see in the cards and in the table the values for the PM are always giving half of the values of the current july values.

         

        can you share a sample data and expected results please.

         

        Formulas are the ones below:

        MTD values = TOTALMTD(SUM('Table'[Value]);'Table'[Date])

MTD PM = CALCULATE([MTD values]; DATEADD('Table'[Date]; - 1; MONTH))

         

        This is just an example and may not work based on your data if you can give any further information

         

         

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Can you use TOTALMTD   with something like DATEADD('your date field',-1,MONTH )?