cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Current Month , Previous Month and Before Previous Month

HI  PBI Experts ,

Here is my question for you in dax ,

 

i want to know the sales of the "current month", "last month", "last before month sales"  by using the Dax. 

i am using previousmonth dax function  and i want to know for  current month  , before previous month dax function.


 

currnt month prv month .jpg 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

will give kudos

Advance Thanks,

Thanks 

sandeep

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Current Month , Previous Month and Before Previous Month

Hi @avulasandeep,

 

If you only want to calculate the sales of "current month", "last month", "last before month sales"  by using the Dax, you could try the formula below.

 

current_month_sales = CALCULATE(SUM('Table1'[Sales]),FILTER('Table1',MONTH('Table1'[Month])=MONTH(TODAY())))

last_month_sales = var current_month= MONTH(TODAY()) return  CALCULATE(SUM('Table1'[Sales]),FILTER('Table1',MONTH('Table1'[Month])=current_month -1))

last before_month_sales = var current_month= MONTH(TODAY()) return  CALCULATE(SUM('Table1'[Sales]),FILTER('Table1',MONTH('Table1'[Month])=current_month -2))

Here is the output.

 

Capture.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Ross73312 Super Contributor
Super Contributor

Re: Current Month , Previous Month and Before Previous Month

Rather than using DAX for this solution, i'd suggest instead creating a Date Table and store a True/False value against the dates that conform to the periods you care about.  You would end up with 3 different flags.  Then you can create 3 visuals based on the "Sum" of sales, and apply those flags to constrain the dates to the 3 different date ranges.

 

In Power Query you can get todays date by using:

Date.From(DateTime.LocalNow())

You can extract years, months and days from this.  An example of a previous month True/False would be:

 

if [Year] = Date.Year(DateTime.LocalNow()) then if Date.Month(DateTime.LocalNow()) = 1 then [Month Number] = 12 else [Month Number] = (Date.Year(DateTime.LocalNow()) - 1) else "FALSE"

In the code above, this is a column added to my date table that has a YEAR and MONTH column.


   

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

Proud to be a Datanaut!


   


Re: Current Month , Previous Month and Before Previous Month

i need all the below selected months in dax . 

 

That means
Current month sales using dax :

previous month sales using dax :

before previous month  sales using dax :

 

Thanks 

sandeep

Community Support Team
Community Support Team

Re: Current Month , Previous Month and Before Previous Month

Hi @avulasandeep,

 

If you only want to calculate the sales of "current month", "last month", "last before month sales"  by using the Dax, you could try the formula below.

 

current_month_sales = CALCULATE(SUM('Table1'[Sales]),FILTER('Table1',MONTH('Table1'[Month])=MONTH(TODAY())))

last_month_sales = var current_month= MONTH(TODAY()) return  CALCULATE(SUM('Table1'[Sales]),FILTER('Table1',MONTH('Table1'[Month])=current_month -1))

last before_month_sales = var current_month= MONTH(TODAY()) return  CALCULATE(SUM('Table1'[Sales]),FILTER('Table1',MONTH('Table1'[Month])=current_month -2))

Here is the output.

 

Capture.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

gdexter Frequent Visitor
Frequent Visitor

Re: Current Month , Previous Month and Before Previous Month

Hi Power BI community,

 

Just a question regarding a small alteration to the question hear.

 

What should be typed in DAX if instead of basing the data around "current month" we want to use the month selected on a slicer as the benchmark?

 

Thanks,

Gdexter

robertsolczak Frequent Visitor
Frequent Visitor

Re: Current Month , Previous Month and Before Previous Month

Hello, I just wonder, why do you use the 'FILTER' function within 'CALCULATE' - does it have any purpose?

 

I'm asking because for me the formula works without it:

 

Volume 2 Months Before:=CALCULATE(COUNTROWS(Table),MONTH(Table[Date])=MONTH(TODAY())-2)
Ross73312 Super Contributor
Super Contributor

Re: Current Month , Previous Month and Before Previous Month

@robertsolczak there are some filter scenarioes where FILTER is 100% required.  From discussions i've had with other datanauts, there is an argument to be made to use the FILTER inside the calculate for optimisation as the filter is used behind the scenes regardless.

 

I still personally avoid filter unless absolutely necessary as it feel it makes for shorter cleaner code, however I am concious when i'm optmising my model, that that is a place for me to review.

 


   

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

Proud to be a Datanaut!


   


robertsolczak Frequent Visitor
Frequent Visitor

Re: Current Month , Previous Month and Before Previous Month

@Ross73312 thanks for such a quick response! I'm also a fan of having the code leaner, but I'm now curious of the cases where you need FILTER - could you send me link to such discussion you mentioned?

Ross73312 Super Contributor
Super Contributor

Re: Current Month , Previous Month and Before Previous Month

Off the top of my head, 2 examples would be if you have complex AND and OR statements.  Also if you do a calculatetable and place it into a variable.


   

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

Proud to be a Datanaut!


   


SpaceCat42 Frequent Visitor
Frequent Visitor

Re: Current Month , Previous Month and Before Previous Month

 

This works....

  • Current Month Value = CALCULATE(SUM(Table1[Value]),MONTH(Table1[Originated])=MONTH(TODAY()))

This does not... Know why is does this? 

  • previous month = var current_month = MONTH(TODAY())
    return CALCULATE(SUM('Table1'[Value]),FILTER('Table1',MONTH('Table1'[Originated])=current_month-1))
    Demo.JPG

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 547 members 4,217 guests
Please welcome our newest community members: