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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Oros
Resolver III
Resolver III

Exclude current month

Hello,

I have a monthly sales data in the last 3 years.  I would like to create line chart that shows monthly sales in the last 3 years.

 

What would be the measure to exclude the current month, or to exclude if the month is still not finished? Thanks.

Oros_0-1718583475780.png

 

For example, for the 2024, since June is still not finished, the 2024 chart line should stop in the month of May.

Oros_1-1718583631283.png

 

 

 

2 ACCEPTED SOLUTIONS
manvishah17
Responsive Resident
Responsive Resident

Hi @Oros ,
You just nned to apply filter in your DAX Measure 

 

Sales_Excluding_Current_Month = 
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
RETURN
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        Sales,
        YEAR(Sales[Date]) < CurrentYear ||
        (YEAR(Sales[Date]) = CurrentYear && MONTH(Sales[Date]) < CurrentMonth)
    )
)

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

johnbasha33
Super User
Super User

@Oros 
you should use a date table/Calender table and use the measure below.

Total Sales =
VAR CurrentMonth = MAX('Date'[Date])
VAR LastMonth = CALCULATE(MAX('Date'[Date]), ALL('Date'))
RETURN
IF(
CurrentMonth = LastMonth,
BLANK(), -- Exclude current month if it's the last available month
CALCULATE(
SUM('Sales'[SalesAmount]),
FILTER(
ALL('Date'),
'Date'[Date] <= LastMonth -- Include months up to the last completed month
)
)
)

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

View solution in original post

4 REPLIES 4
johnbasha33
Super User
Super User

@Oros 
you should use a date table/Calender table and use the measure below.

Total Sales =
VAR CurrentMonth = MAX('Date'[Date])
VAR LastMonth = CALCULATE(MAX('Date'[Date]), ALL('Date'))
RETURN
IF(
CurrentMonth = LastMonth,
BLANK(), -- Exclude current month if it's the last available month
CALCULATE(
SUM('Sales'[SalesAmount]),
FILTER(
ALL('Date'),
'Date'[Date] <= LastMonth -- Include months up to the last completed month
)
)
)

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Hi @johnbasha33 ,

 

Thanks for helping.  Your solution works as well!

manvishah17
Responsive Resident
Responsive Resident

Hi @Oros ,
You just nned to apply filter in your DAX Measure 

 

Sales_Excluding_Current_Month = 
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
RETURN
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        Sales,
        YEAR(Sales[Date]) < CurrentYear ||
        (YEAR(Sales[Date]) = CurrentYear && MONTH(Sales[Date]) < CurrentMonth)
    )
)

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Hi @manvishah17,

 

It works!!! Thanks a lot!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors