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

[BBECQ]

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]))

)

[Poojara_D12]

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 

[pcoley]

@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😀

[BBECQ]

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

[Anonymous]

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.

[Kedar_Pande]

@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