Dateadd for incomplete months

[moezsajwani]

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

[HotChilli]

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

[MFelix]

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]

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]

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

[baravo]

Hi @MFelix , I say yes to your question: "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?"

When I select the most current date, i.e. 20/07, I want to compare it with the first 20 days of June. The DATEADD, as well as the TOTALMTD work fine with all other dates except for the current date (which is MAX date in the calendar). When the current date is selected, instead of comparing the first 20 days, it takes the whole previous month (whole June).

I saw in your earlier post that you suggested a workaround, but it doesn't work for me. 

Please find photos attached. Current date is 25/07. Until 24/07 it works fine, but 25/07 it takes the whole previous month, which is wrong.

[baravo]

I solved it!

The problem was that my calendar date was related to another table (invoices) with MIN and MAX date values:

Calendar = CALENDAR(MIN(Invoices[date]),MAX(Invoices[date]))

I changed my Calendar Date to this:

Calendar = CALENDAR(MIN(Invoices[date]),DATE(2021,31,12))

and limited the dates in filter by Relative Date for the last 3 years, so I only see the past till today.

It works!

[MFelix]

Hi @baravo ,

Just on suggestion, you should make your start date on the calendar starting on January 1st and end date for the calendar dinamic so that you don't have to change it in the future, you have:

Calendar = CALENDAR(MIN(Invoices[date]),DATE(2021,31,12))

I suggest you change it to  something similar to this:

Calendar = CALENDAR(DATE(YEAR(MIN(Invoices[date])),1,1),DATE(YEAR(TODAY())+1,31,12))


or 

Calendar = CALENDAR(DATE(YEAR(MIN(Invoices[date])),1,1),DATE(YEAR(TODAY()),31,12))

This will allow you to have a more dinamic value in the future, and don't have to go back to the model to change the calendar table.