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

[srikanth571]

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

[srikanth571]

Hi, 

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

[v-cgao-msft]

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

[srikanth571]

Hi @lbendlin, 

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

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

[lbendlin]

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-...

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/1447...