topic Need to calculate sales for current month, next month and next 6months (26 weeks+current week) in DAX Commands and Tips

Need to calculate sales for current month, next month and next 6months (26 weeks+current week)

srikanth571 — Sun, 11 Aug 2024 05:22:22 GMT

Hello all,

Need to calculate sales for current month, next month and next 6months (26 weeks including current week)

Bleow is the example:

sales for current month Aug = 1800

sales for Next month Sep = 2816

sales for Next 6 months (26 weeks starting from 05-08-2024) = 12672

Re: Need to calculate sales for current month, next month and next 6months (26 weeks+current week)

lbendlin — Sun, 11 Aug 2024 17:12:32 GMT

Weeks and months are incompatible. Is your data on day level?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Need to calculate sales for current month, next month and next 6months (26 weeks+current week)

srikanth571 — Mon, 12 Aug 2024 03:33:11 GMT

Hi lbendlin,

kindly refer below link to access the sample data for above requirement.

https://drive.google.com/file/d/1hGgPmGMSKZ41DCzQR7xj15pNxMFM7sDD/view

Re: Need to calculate sales for current month, next month and next 6months (26 weeks+current week)

Anonymous — Tue, 13 Aug 2024 05:43:00 GMT

Hi srikanth571 ,

For [CurrentMonthSales], [NextMonthSales] we can create a new calendar table like this:

Then create a relationship between [Date] and [Week]:

Then please create follow measures:

CurrentMonthSales = CALCULATE( SUM('Table'[Sales]), 'Calendar'[YearMonthIndex] = YEAR(TODAY())*12+MONTH(TODAY()) )

NextMonthSales = CALCULATE( SUM('Table'[Sales]), 'Calendar'[YearMonthIndex] = YEAR(TODAY())*12+MONTH(TODAY()) + 1 )

For [Next6MonthsSales], I observe that Week is calculated from the 1st of the month, and here I am assuming that the actual data is continuous as you have given. I have added a new [WeekIndex] column in PowerQuery:

Then create this measure:

Next6MonthsSales = VAR __cur_week_index = CALCULATE(MAX('Table'[WeekIndex]), 'Calendar'[Date]<=TODAY()) VAR __result = CALCULATE(SUM('Table'[Sales]),'Table'[WeekIndex]>=__cur_week_index && 'Table'[WeekIndex]<=__cur_week_index+25) RETURN __result

output:

Best Regards,
Gao
Community Support Team

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!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: Need to calculate sales for current month, next month and next 6months (26 weeks+current week)

srikanth571 — Fri, 16 Aug 2024 10:38:56 GMT

Hi,

I was able to figure out from other forum topics, thanks all for your help.