Sum sales between a set date and today (TY and LY)

[APM]

Hello,

I'm trying to calculate sales for the season TY and LY. The season starts on December 1st of the prior year.

I'm using these formulas:

2021 Season Sales $ =

CALCULATE(

    SUM(STD_RETAIL_SALES_REPORT[Selling_Price]),

    DATESBETWEEN('Date'[Date].[Date],12/1/2020,DATE(YEAR(NOW())-1,MONTH(NOW()), DAY(NOW()-1))

))

2022 Season Sales $ =

CALCULATE(

    SUM(STD_RETAIL_SALES_REPORT[Selling_Price]),

    DATESBETWEEN('Date'[Date].[Date],12/01/2021,TODAY()))

The results are wildly incorrect.

What am I doing wrong?

My data:

And I have a simple date table and a relationship between that date table and the 'Action_Date' field.

 

Thanks for your help!

[v-angzheng-msft]

Hi, @APM 

You can try using these two functions to help you.

EOMONTH

EDATE

example:

https://community.powerbi.com/t5/Desktop/Add-Results-of-a-different-Measure-for-last-12-months/m-p/2...

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@APM , if there is not filter used

this year =

CALCULATE(
SUM(STD_RETAIL_SALES_REPORT[Selling_Price]),filter('Date', year('Date'[Date]) = Year(Today())))

last year =

CALCULATE(
SUM(STD_RETAIL_SALES_REPORT[Selling_Price]),filter('Date', year('Date'[Date]) = Year(Today()) -1 ))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[APM]

Hey,

But where in the formula might I specify that the period start is 12/1/2020 and 12/01/2021?

I tried to adapt your formula to this with no luck:

2021 STD $ =

CALCULATE(

SUM(STD_RETAIL_SALES_REPORT[Selling_Price]),filter('Date','Date'[Date] >= 12/1/2020),FILTER('Date',DATE(YEAR(NOW())-1,MONTH(NOW()), DAY(NOW()-1))))

[APM]

In essence,

I just want to be able to obtain sales from

12/1/2020 to 'today' last year (and for today that would be 3/28/2021)

and

12/1/2021 to 'today' (3/28/2022).

In excel this would be absurdly simple, in DAX it's turning out to be quite the conundrum.