Help Needed - Sales from Past Month and Past Year Month

[Anonymous]

Hi,

I currently have a multi-row card that would require me to calculate the % Var of Volume vs Past Month. However it is currently comparing with the absolute value (total value from last month) to the MTD value of this month. If I currently have 9 selling days for the month, how can I modify measure in such a way that i would retrieve only the first 9 selling days of the month last month? how about last year same month? (ex. current month is sept, retrieve sept from last year first 9 selling days)

Here are the current measures that I have so far

Volume MTD = calculate([Volume],DATESMTD('PH_RAW_SALES'[Invoice date]))

Volume PM = calculate([Volume],PREVIOUSMONTH(DATE_TABLE[Date]))

Volume PYM = CALCULATE([Volume],ALL(DATE_TABLE),DATESBETWEEN(DATE_TABLE[Date],[PYM StartDate],[PYM EndDate]))

PYM StartDate =date(year(eomonth([toDate],-12)),month(eomonth([toDate],-12)),1)

PYM EndDate =EOMONTH([PYM StartDate],0)

MTD Days = calculate([Total Selling Days],

    DATESBETWEEN('DATE_TABLE'[Date],

    STARTOFMONTH('DATE_TABLE'[Date]),

    min(ENDOFMONTH('DATE_TABLE'[Date]),calculate([DateLastRefreshed],all(PH_RAW_SALES)))))

[parry2k]

@Anonymous Yes in my example I just showed made that as fixed data but it could be any date. 

Did you mark your date table as a date table?

[Anonymous]

Hi Parry,

I now have this version of my dax.

Volume PM MTD =

VAR _date = TODAY()

VAR _maxdate = MAX(DATE_TABLE[Date])

VAR _comparedate =

    IF (

        DAY(_date) = DAY(EOMONTH(_date, 0)), // Check if it's the end of the current month

        EOMONTH(_date, -1), // Calculate the last day of the previous month

        DATE(YEAR(_date), MONTH(_date), 1) - 1 // Calculate the last day of the previous month

    )

RETURN

    CALCULATE (

        [Volume], // Your volume measure

        DATESMTD (

            CALCULATETABLE (

                VALUES (DATE_TABLE[Date]), // Get a table of unique dates from DATE_TABLE

                KEEPFILTERS (DATE_TABLE[Date] <= _comparedate)

            )

        )

    )

However, it seems to be returning the current month MTD for august, where as it should be returning the past month MTD. For september, it is returning blanks. What could be the cause of this?

And yes I have marked my date table as a date table. Thank you!

[parry2k]

@Anonymous check this video on my YT channel which addresses the same exact question: Learn how to compare MTD sales as of date with same number of days last year - Part 7 - YouTube 

[Anonymous]

Hi Parry,

Thanks for the suggestion. However, there's a part which confuses me.

In the first line, you put here January 10, 2022, so it made the formula static to compute all the way to the 10th for every month. How should it be replaced if i want it to compute as the days go by?

I do have an idea of how the logic is supposed to work for the supposed dax for this, however i cannot translate to DAX Formula as easily.

1. Compute MTD Days current month

2. Identify start of month last month, then calculate sum volume for date start + MTD selling days -1

I do aplogize if I wasnt able to fully understand from the video and hoping you could still help with this one 🙂

[Anonymous]

@parry2k Hi Parry,

I tried using the exact formula however, the results return blanks. What could be the cause?

Volume PM MTD =

VAR _date =

    TODAY ()

VAR _maxdate =

    MAX ( DATE_TABLE[Date] )

VAR _endofmonth =

    CALCULATE ( MAX ( DATE_TABLE[Date] ), ALLSELECTED ( DATE_TABLE ) ) == _date

VAR _comparedate =

    IF (

        _endofmonth,

        _maxdate,

        MIN ( DATE ( YEAR ( _maxdate ), MONTH ( _maxdate ), DAY ( _date ) ), _maxdate )

    )

RETURN

    CALCULATE (

        [Volume],

        DATESMTD (

            CALCULATETABLE (

                VALUES ( DATE_TABLE[Date] ),

                KEEPFILTERS ( DATE_TABLE[Date] <= _comparedate )

            )

        )

    )