topic Re: Creating a visual line graph with cumulative sales for same month 1 year ago - ENTIRE MONTH in DAX Commands and Tips

Creating a visual line graph with cumulative sales for same month 1 year ago - ENTIRE MONTH

BBECQ — Tue, 24 Dec 2024 08:53:28 GMT

Hi,

This DAX formula already give me the cumulative sales for same month 1 year ago, for all dates until today 1 year ago.
However, since all sales are known for the entire month 1 year ago, I would like to see the full month cumulative sales.
I can't seem to find how to adapt this formula to keep providing data points until the last day of the month

Cumulative Sales LY =

VAR _maxdate = MAX('CSV Import Daily'[DATE])

RETURN

CALCULATE(

SUM('CSV Import Daily'[Sales Result]),

ALLSELECTED('CSV Import Daily'),

'CSV Import Daily'[DATE] <= _maxdate,

SAMEPERIODLASTYEAR(DATESMTD('CSV Import Daily'[DATE]))

)

Re: Creating a visual line graph with cumulative sales for same month 1 year ago - ENTIRE MONTH

Kedar_Pande — Tue, 24 Dec 2024 10:55:15 GMT

BBECQ

Cumulative Sales LY Full Month =
CALCULATE(
SUM('CSV Import Daily'[Sales Result]),
SAMEPERIODLASTYEAR(DATESMTD('CSV Import Daily'[DATE]))
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Re: Creating a visual line graph with cumulative sales for same month 1 year ago - ENTIRE MONTH

BBECQ — Tue, 24 Dec 2024 13:20:53 GMT

Thanks!!
What I notice with your formula is that on today (Dec 24th) the cumulative total already in includes sales from 26-27 & 28/12/2023 ... Bizar

Re: Creating a visual line graph with cumulative sales for same month 1 year ago - ENTIRE MONTH

pcoley — Tue, 24 Dec 2024 15:16:08 GMT

BBECQ please try with PARALLELPERIOD instead of SAMEPERIODLASTYEAR in your formula.

CALCULATE ( ...,
PARALLELPERIOD ( DATESMTD('CSV Import Daily'[DATE]), -1, YEAR ) )

I hope this helps you, if so please accept the solution. Kudos are welcome😀

Re: Creating a visual line graph with cumulative sales for same month 1 year ago - ENTIRE MONTH

Anonymous — Wed, 25 Dec 2024 07:56:54 GMT

Thanks for the reply from pcoley and Kedar_Pande , please allow me to provide another insight:

Hi, BBECQ
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.Firstly, I created the following test data:

2.Secondly, I modified the measures as follows:

Cumulative Sales LY = VAR aa = CALCULATE ( SUM ( 'CSV Import Daily'[Sales Result] ), SAMEPERIODLASTYEAR ( DATESMTD ( 'CSV Import Daily'[DATE] ) ) ) RETURN IF ( MAX ( 'CSV Import Daily'[Date] ) = CALCULATE ( MAX ( 'CSV Import Daily'[Date] ), ALL ( 'CSV Import Daily' ) ), aa - CALCULATE ( SUM ( 'CSV Import Daily'[Sales Result] ), FILTER ( ALL ( 'CSV Import Daily' ), 'CSV Import Daily'[Date] > EDATE ( TODAY (), -12 ) && 'CSV Import Daily'[Date] <= EOMONTH ( TODAY (), -12 ) ) ), aa )

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

Re: Creating a visual line graph with cumulative sales for same month 1 year ago - ENTIRE MONTH

Poojara_D12 — Wed, 25 Dec 2024 08:26:47 GMT

Hi BBECQ

To show the full cumulative sales for the same month last year, update your DAX formula like this:

Cumulative Sales LY = CALCULATE( SUM('CSV Import Daily'[Sales Result]), ALLSELECTED('CSV Import Daily'), DATESMTD(SAMEPERIODLASTYEAR('CSV Import Daily'[DATE])) )

For dynamic "up to today's date" while showing the full last year month:

Cumulative Sales LY = VAR _maxdate = MAX('CSV Import Daily'[DATE]) RETURN CALCULATE( SUM('CSV Import Daily'[Sales Result]), ALLSELECTED('CSV Import Daily'), SAMEPERIODLASTYEAR( DATESBETWEEN( 'CSV Import Daily'[DATE], STARTOFMONTH(_maxdate), ENDOFMONTH(_maxdate) ) ) )

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS