Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
will give kudos
Advance Thanks,
Thanks
sandeep
Solved! Go to Solution.
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.
Best Regards,
Cherry
this is a very good example for such cases... I have implemented it and using it...
This works....
This does not... Know why is does this?
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.
Best Regards,
Cherry
Thanks a lot, this works like magic. Though I need a small help further. I have more than 1 year's data in the query. So the current_month-1 picks all months that are falling into this. E.g. Month(Today()) returns 9 for September, then the formula picks "8" which is August of 2022, 2021 both in my case. Any ideas on how to tackle this?
Thanks
Sangeeta
Add another var current_year = YEAR(TODAY()) and then add an additional filter in CALCULATE
I have same problem ... please help
Thanks guys,
I have a similar issue. How I can accomplish the cumulative SUM of ALL previous months and to dynamically show this SUM as of current month?
will this work if the month is january ? (with several decembers for each year in the data)
Hi, would like to ask what is the right syntax if you have a slicer for date, and the analysis will automatically show output based on the date/month selected?
Example if you choose april 2022 in the slicer and then the output should be March 2022 (last month) and Feb 2022 (last 2 months)
thank you!
Right, for me and what I was trying to do was to identify with a 1-Yes, 0-No what the is current month on my calendar table and MONTH(TODAY() -1 was not taking into account the year when rolling into the new year so I used the below.
Hi.. Can everyone help me how i can do powerbi for previous month based on data given.
Thank you.
Is your Month field a date and this is the formatting (January-21)? If so you can reference that field using the PREVIOUSMONTH(DateTime'[DateKey]) if you just want the previous month date. If you are looking to get the "Amount(s) RM" from previous month then use = CALCULATE(SUM(yourtablename[Amount(s) RM]), PREVIOUSMONTH(yourtablename[Month]))
Hi Fo88er,
what do means youtablename ... Sales Detail ?
Thanks.
How do you handle last month sales for Dec 2021 when we are in Jan 2022? I am trying to find a good is last month logic that take into account when we roll into a new year. The below works if in current year (multiple years of dates in the dataset)
I have the same problem as you mentioned with the new year.
It's not clear how you wanna integrate:
Yes, I used this
Try this:
Measure =
VAR _index = 0
VAR _StartMonth = DATE( YEAR( TODAY() ), MONTH( TODAY() ) - _index , 1)
VAR _StartNextMonth = EOMONTH( _StartMonth, 0) + 1
VAR _Amount = CALCULATE( [Sales], Calendar[Date] >= _StartMonth, Calendar[Date] < _StartNextMonth)
RETURN _Amount
For each of the three measures you are trying to make, you just change the _index:
and so on... You can apply for how many months before or after you want. Hope I helped.
Seya!
I cannot understand why the LM value does not appear in the yellow cell when I apply the external filter. Can anyone help me? I already appreciate any help please.
I enclose the data file for you to analyze.
File: https://www.dropbox.com/s/c8plvgfrotm1kqr/Dados.xlsx?dl=0
Thank you so much, this helped me solve my issue as well!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!