Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sales Last month and Last month same period

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))
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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

 

SimonePalmas45_0-1675834198759.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.