Sales Last month and Last month same period

[Anonymous]

Hi, 

I need a card with Sales this month (today is the 8th of February 2023), one with Sales Previous Month (January 2023) and one with the previous month same period (from the first of January to the 8th of January).

I used the code beneath. Is it correct? If not, why, and what is the best solution? 

Thank you!!

This Month Sales = CALCULATE(SUM('Dataset'[Sales]),

                                        'Date'[Year]= YEAR(TODAY()),

                                        'Date'[Month No] = MONTH(TODAY()))

Last Month Sales =

var Current_date = DATE(YEAR(TODAY()), MONTH(TODAY())-1,DAY(TODAY()))

return

CALCULATE(SUM('Dataset'[Sales]),

                              'Date'[Year] = YEAR(Current_date),

                              'Date'[Month No] = MONTH(Current_date))

Last Month Sales (Same period) =

var Current_date = DATE(YEAR(TODAY()), MONTH(TODAY())-1,DAY(TODAY()))

return

CALCULATE(SUM('Dataset'[Sales]),

                              'Date'[Year] = YEAR(Current_date),

                              'Date'[Month No] = MONTH(Current_date),

                              'Date'[Day]<=DAY(Current_date))

[OwenAuger]

@Anonymous 

Apologies, yes you're quite correct.

I'll admit I had skim-read part of your code 🫢

As you've described, within the DATE function, the month & day arguments act as "offsets", that can take any positive/negative integer relative to month 1 or day 1. So subtracting 1 from month 1 will work fine.

So your measures should work fine, provided that you aren't filtering on columns of 'Date' outside those mentioned in the measures.

As a precaution, I would normally add ALL ( 'Date' ) or REMOVEFILTERS ( 'Date' ) as an additional argument within CALCULATE, just in case any other filters are applied on columns of 'Date'.

Regards

[OwenAuger · Accepted solution]

Hi @Anonymous 

I would suggest writing these slightly differently.

Some issues with your original measures:

• Subtracting one from Month 1 (January) will result in Month 0 which doesn't make sense, and would return a blank result.
• If you are apply filters on columns of the 'Date' table other than 'Date'[Date], you need to remove existing filters with ALL () or REMOVEFILTERS (). If your 'Date' table is marked as a date table and filters are applied on the 'Date'[Date] column, ALL/REMOVEFILTERS is not needed.

First of all, to make sure the below measures work, ensure that your 'Date' table is marked as a date table (see here).

This Month Sales =
CALCULATE (
    SUM ( 'Dataset'[Sales] ),
    PARALLELPERIOD ( 'Date'[Date] = TODAY (), 0, MONTH )
)

Last Month Sales =
CALCULATE (
    SUM ( 'Dataset'[Sales] ),
    PARALLELPERIOD ( 'Date'[Date] = TODAY (), -1, MONTH )
)

Last Month Sales (Same period) =
CALCULATE (
    SUM ( 'Dataset'[Sales] ),
    DATEADD ( DATESMTD ( 'Date'[Date] = TODAY () ), -1, MONTH )
)

PARALLELPERIOD returns complete periods (in this case complete months) containing a given set of dates, with a given offset. So offset = 0 gives the full month containing TODAY(), and offset = -1 gives the month before the full month containing TODAY().

DATESMTD returns the set of dates in the "month-to-date" period ending on the max date in a given set of dates, in this case month-to-date up to TODAY(). Then DATEADD is used to shift this range one month earlier.

Do these work for you?

Regards,

Owen

[Anonymous]

Thank you @OwenAuger,

Using var Current_date = DATE(YEAR(TODAY()), MONTH(TODAY())-1,DAY(TODAY()))

makes me avoid ending up with month <1, Power BI recognises that and if today's month is January it returns 12 and not 0;

Also,  'Date'[Date] is marked as date table and I am not filtering the other columns;

Provided that your solution is better than mine, would my solution still work? (I wrote like 25 measures with it and would not like to change them all).