Future Month YTD calculation

[Anonymous]

Hi Community..!!

My requirement is to calculate future YTD.

For Ex: Now we are in month of May 2024 , I need to calculated YTD starting from July month + 5 month 

its need to start from July month why because , the requirement is like latest month (May) + 2 months = July month,

if we are in June month then it will start from August month like this... From August + 5 months = From August to Jan 2025

I have future Data.

Could anyone help me to resolve this.

Thanks in advance..!!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Thanks for the reply from @amitchandak .

Here is the sample data I created:

Date	Sales
1/1/2024	100
2/1/2024	200
3/1/2024	150
4/1/2024	250
5/1/2024	160
6/1/2024	240
7/1/2024	320
8/1/2024	280
9/1/2024	420
10/1/2024	310
11/1/2024	180
12/1/2024	280
1/1/2024	100
2/1/2024	200
3/1/2024	150
4/1/2024	250
5/1/2024	160
6/1/2024	240
7/1/2024	320
8/1/2024	280
9/1/2024	420
10/1/2024	310
11/1/2024	180
12/1/2024	280

Create a disconnected date table and create a month column in both the date table and the master table:

Date = CALENDAR(MIN('Sales'[Date]),MAX('Sales'[Date]))

Month = MONTH('Date'[Date])

Month = MONTH('Sales'[Date])

Create a measure in the Sales table:

Accumulated Sales =
VAR _curMon = MAX('Date'[Month])
VAR _staMon = _curMon + 2
VAR _endMon = _staMon + 5
RETURN
CALCULATE(
SUM('Sales'[Sales]),
FILTER(
ALL('Sales'),
'Sales'[Month] >= _staMon
&&
'Sales'[Month] <= _endMon
))

This measure calculates the Sum of Sales from the next two months to the next six months of the current month.

The visual effect of the page is as follows:

If your problem cannot be solved successfully, please provide me with sample data or pbix file. Please be careful to erase sensitive information and do not log in to your account when uploading the pbix file in Power BI Desktop.

The pbix file is attached.

Best Regards,
Yang
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!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Thanks for the reply from @amitchandak .

Here is the sample data I created:

Date	Sales
1/1/2024	100
2/1/2024	200
3/1/2024	150
4/1/2024	250
5/1/2024	160
6/1/2024	240
7/1/2024	320
8/1/2024	280
9/1/2024	420
10/1/2024	310
11/1/2024	180
12/1/2024	280
1/1/2024	100
2/1/2024	200
3/1/2024	150
4/1/2024	250
5/1/2024	160
6/1/2024	240
7/1/2024	320
8/1/2024	280
9/1/2024	420
10/1/2024	310
11/1/2024	180
12/1/2024	280

Create a disconnected date table and create a month column in both the date table and the master table:

Date = CALENDAR(MIN('Sales'[Date]),MAX('Sales'[Date]))

Month = MONTH('Date'[Date])

Month = MONTH('Sales'[Date])

Create a measure in the Sales table:

Accumulated Sales =
VAR _curMon = MAX('Date'[Month])
VAR _staMon = _curMon + 2
VAR _endMon = _staMon + 5
RETURN
CALCULATE(
SUM('Sales'[Sales]),
FILTER(
ALL('Sales'),
'Sales'[Month] >= _staMon
&&
'Sales'[Month] <= _endMon
))

This measure calculates the Sum of Sales from the next two months to the next six months of the current month.

The visual effect of the page is as follows:

If your problem cannot be solved successfully, please provide me with sample data or pbix file. Please be careful to erase sensitive information and do not log in to your account when uploading the pbix file in Power BI Desktop.

The pbix file is attached.

Best Regards,
Yang
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!

[Anonymous · Accepted solution]

Hi @Anonymous 

Thanks for the solution.

There is bit change in the calculation as below code,

Spend Optimize month range 2-6 =

var selected_month = MAX('Automated Monthly'[Month-Year])

var futureTwoMonths = EOMONTH(selected_month,1) + 1

var futureSixMonths = EOMONTH(futureTwoMonths,5)

var result = CALCULATE(SUM('Automated Monthly'[Optimized Spend]),

                        'Automated Monthly'[Month-Year] >= futureTwoMonths &&  'Automated Monthly'[Month-Year] <= futureSixMonths)        

RETURN result

To create var futureTwoMonths we have to use +1 at the end
that time works for me.

Thank u so much..!!

[Anonymous]

Hi @Anonymous 
Thanks for the solution,

Here i am using slicer as a input for month

the month will be selected from slicer,

If i select Jan month in the slicer then the calculation will start from March to Aug 
if i am using the below dax code shared by you 

VAR _staMon = _curMon + 2
VAR _endMon = _staMon + 5

then it is showing emty result ,

if i use below code 

var selected_month = SELECTEDVALUE('Automated Monthly'[Month-Year])

var futureTwoMonths = EOMONTH(selected_month,2)

var futureSixMonths = EOMONTH(futureTwoMonths,5)

then EOMONTH dax is taking last date of the month , because of that the values are getting different.

the calculation need to be start from 1st day of the month.

and needs to be end at last day of the month 

ex : March 1st to Aug 31st

Please let me know if you have any doubts.

Thank you so much..!!

[amitchandak]

@Anonymous , Assuming you are using a date table joined with date of your table

Try a measure like

Custom YTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max1)+1
var _max = eomonth(_max1,7)

return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi @amitchandak 
You have calculated [net] in return field, what is the formula for the same.

and i am selecting month from slicer , hence in the calculation also, if the month i have selected in slicer is Jan then the calculation will be start from month March and till 6 month ie. March - August.

I need cumulative sum for the same.

Thanks