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
ghosh_kavitha
Helper IV
Helper IV

Nextmonth calculation

Hi,

ghosh_kavitha_0-1594980302434.png

This is my table.I just want to calculate Aug month total hours value.

I used this measure 

Next month value = calculate(sum('table'[total hours value]),dateadd(table[month year],+1,month

 

can someone help.

 

thanks

kg

1 ACCEPTED SOLUTION

@ghosh_kavitha , This wrong usgaes. If you need just to diaply next month use this 

next month = format(eomonth(today(),1),"MMM-YYYY")

View solution in original post

19 REPLIES 19
Pragati11
Super User
Super User

Hi @ghosh_kavitha ,

 

Create a measure as follows:

next month sales = CALCULATE(SUM(ClothingSales[Sales]), DATEADD(ClothingSales[Date], 1, MONTH))
 
Just use your dates column in above dax.
 
Thanks,
Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Thanks Pragati..its not working

Can you help me to calculate the next month measure alone.

 

ans should be "Aug 2020".

 

i tried Next month = month(today()+1)

the ans was "8"..but i need Aug 2020

 

Thanks

Kg

You cannot use DateAdd with Measure....

You need to create calculated column to get the next month Value.

 

Create a new Table name it "Date" using "CalendarAuto()"

NewTable.png

 

 

CalendarAuto.png

Now Join this date with your  Table (MonthYear and Date)

JoinTable.png

 

Mark this new date table as "Date Table"

 

Mark As Date Table.png

 

Create measure to calculate next month value

Next month value = calculate(sum('table'[total hours value]),DATESINPERIOD(DimDate[Date],MAX(DimDate[Date]),1,MONTH))

 

 

 

 







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

Proud to be a Super User!




Hi @ghosh_kavitha ,

 

You can use following measure to get next month value:

Next month = DATEADD(ClothingSales[Date], 1, MONTH)
 
Replace ClothingSales[Date]  in above dax with your date column.
 
Also, please try tagging people you are trying to respond from next time.
 
Thanks,
Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi

ghosh_kavitha_1-1594986371518.pngghosh_kavitha_2-1594986394791.png

this is my actual problem.

Thanks

Kg

@ghosh_kavitha , That is wrong, why are you using month year in dateadd function. 

you need to use date. Same has been provided in all formulas 

Still same

ghosh_kavitha_1-1594987564738.pngghosh_kavitha_2-1594987577310.png

 

@ghosh_kavitha , This wrong usgaes. If you need just to diaply next month use this 

next month = format(eomonth(today(),1),"MMM-YYYY")

Amit,

Thanks a ton.

here is my solution.

 

NEXT MONTH = FORMAT(EOMONTH(TODAY(),1),"MMM YYYY")
 
NEXT month HOURS = CALCULATE(SUM('Sales - Hours'[Total Hours Value]),FILTER('Sales - Hours','Sales - Hours'[Month Year]=DATEVALUE([NEXT MONTH])))
 
 
 
Thanks again

Hi @ghosh_kavitha ,

 

Your comment - Not working doesn't give me any insight on what is wrong with it.

Always helpful to have some screesnhot with error.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Its still showing July values not aug values

Hi @ghosh_kavitha ,

 

There is an existing thread for the exact same issue:

https://community.powerbi.com/t5/Desktop/Sales-Sum-of-next-month/td-p/376981

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@ghosh_kavitha , Few things.

1) Your data table should be marked as a date table.

2. Hope Jul 2020 is the month on the page. If not then time intelligence will pick up last month of the calendar

Amit,

ghosh_kavitha_0-1594983329657.png

Date only

amitchandak
Super User
Super User

@ghosh_kavitha ,

 

Next month value =  CALCULATE(sum('table'[total hours value]),DATESMTD(dateadd('Date'[Date],1,MONTH)))

 

CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))

 

Use date table properly

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

Amit 

I tried 

Next month = CALCULATE(SUM('Sales- Hours'[Total Hours Value]),DATESMTD(DATEADD('calender'[Month Year],1,MONTH)))
 
Next month . = CALCULATE(SUM('Sales - Hours'[Total Hours Value]),DATESINPERIOD('calender'[Month Year],MAX('Table'[Month Year]),1,MONTH))
Both are bringing same values ...
Its not working.. means not matching my aug month value.
 
Thanks
Kavitha Ghosh
FarhanAhmed
Community Champion
Community Champion

Next month value = calculate(sum('table'[total hours value]),DATESINPERIOD(DimDate[Date],MAX(DimDate[Date]),1,MONTH))

 

Try use this.







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

Proud to be a Super User!




Thanks Farhan..Its not working

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.