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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.