I’m trying to calculate sales for the same period last month using the formula below:
Income to Date Last Month =
CALCULATE( [Total Sales],
DATEADD( FILTER( DATESMTD(Dates[Date] ), Dates[Date] < TODAY() ), -1, MONTH ))
The problem is that the calculation for last month is a day less. That is, today is Jan 21. The formula is only calculating up to Dec 20. I would like it to calculate up to Dec 21.
I tried using ‘<= TODAY()’ but using this would then give me total sales for all Dec.
Well I tried the same formula as provided by you into my working file and compared it with my formula and both the formulas as providing the same results as expected.
Could you please attach the working file alongwith the description of the results that you’re expecting or trying to achieve? So that members of our forum can assist you in a better and efficient manner.
I’m attaching the working of my PBIX file for the reference.
Thanks for replying. Apologies, should have attached a sample.
For the real life context, I have a report that is updated daily at 1 pm once all today’s sales have been processed. My manager wants to see how we are comparing to last month. Yesterday’s actual figures are below:
She has pointed out to me that last month’s figures are incorrect. That is this month added sales from 01/01/2021 to 21/01/2021. Last month only added 01/12/2020 to 20/12/2020.
I know the problem is my measure for last month because it is ‘< TODAY()’ making it last month yesterday.
I don’t know how to get it so that last month’s calculation would have been 01/12/2020 to 21/12/2020.
I have attached a sample report. My formula has last month same period as 395,340. However, if you add the raw data (01/12/2020 to 22/12/2020), it should be 404,700.
In your “Income to Date Last Month” formula, I just made a minor change in it i.e. previously it was “< TODAY()” and I just inserted “=” sign in it making it as “<= TODAY()”. So below is the revised formula and the screenshot of the results provided for the reference -
Income to Date Last Month =
IF( [Total Revenue] = BLANK() ,
BLANK() ,
CALCULATE( [Total Revenue] ,
DATEADD(
FILTER(
DATESMTD(Dates[Date] ) ,
Dates[Date] <= TODAY() ) ,
-1 ,
MONTH ) ) )
Lastly, before I sign off from this thread, in your initial post you mentioned that in the formula when you make it as “<= TODAY()” it calculates the entire result of the last month which is not true. Becuase when I applied the filter on Dec 2020 the Total Revenue for that entire month was 5,57,419.90. Below is the screenshot provided for the reference
So it’s proved that the revised formula is showing the results in proportion upto the dates of the previous month. That is, till 22/01/2021 the results are shown in proporation w.r.t dates 22/12/2020.
I’m also attaching the working of the PBIX file for the reference.
Hoping you find this useful and meets your requirements that you’ve been looking for.
I can point out another scenario which not consider today() function . Which is data source and Power BI development machine or server in different time zones. So, when you get this type of issue you can check for locale of computer and data source.
