DAX for upcoming 6 months from current month

[neelofarshama]

Hi All,

I have a report with start date, end date, Revenue, Term=start date-end date, Term Revenue=Term/Term Revenue

I want to display current date along with next 6 months dates as shown below. The "Term Revenue" column should be displayed for all these months starting from start date and displaying it.

Please let me know how to achieve this.

Expected Revenue	Start Date	End date	Term	Term Revenue	Current	+6months
					May	June	July	August	September	October	November
15000	7/1/2021	9/30/2021	3	5000	$       -	$       -	$     5,000	$     5,000	$     5,000	$         -	$         -
22,500	10/1/2022	6/30/2022	9	2500	$       -	$       -	$         -	$         -	$         -	$     2,500	$     2,500
2000	12/1/2020	4/30/2021	5	400	$       -	$       -	$       -	$       -	$       -	$       -	$       -
25,000	5/16/2021	12/19/2021	8	3,125	$       3,125	$       3,125	$       3,125	$       3,125	$       3,125	$       3,125	$       3,125
7,010	4/1/2021	11/5/2021	8	876.19	$      876.19	$      876.19	$      876.19	$      876.19	$      876.19	$      876.19	$      876.19

[v-robertq-msft · Accepted solution]

Hi, @neelofarshama 

According to your description, I can finally figure out your requirement, then I think you can change the formula of your 7 calculated columns like this:

Current Month =

var _startdate=DATE(YEAR([Start Date]),MONTH([Start Date]),1)

var _enddate1=EOMONTH(_startdate,6)

var _enddate2=EOMONTH([End Date],0)

var _enddate=IF(_enddate1<=_enddate2,_enddate1,_enddate2)

return

IF(

    TODAY()>=_startdate&&TODAY()<=_enddate,[Term Revenue],BLANK())

Next 1 month =

var _startdate=DATE(YEAR([Start Date]),MONTH([Start Date]),1)

var _enddate1=EOMONTH(_startdate,6)

var _enddate2=EOMONTH([End Date],0)

var _enddate=IF(_enddate1<=_enddate2,_enddate1,_enddate2)

return

IF(

    EOMONTH(TODAY(),1)>=_startdate&&EOMONTH(TODAY(),1)<=_enddate,[Term Revenue],BLANK())

Next 2 month =

var _startdate=DATE(YEAR([Start Date]),MONTH([Start Date]),1)

var _enddate1=EOMONTH(_startdate,6)

var _enddate2=EOMONTH([End Date],0)

var _enddate=IF(_enddate1<=_enddate2,_enddate1,_enddate2)

return

IF(

    EOMONTH(TODAY(),2)>=_startdate&&EOMONTH(TODAY(),2)<=_enddate,[Term Revenue],BLANK())

The rest 4 months are similar as the above DAX formula, you can just follow them to transform the DAX.

Then go to the table chart ,set the summarize type of all these columns to “Sum”:

And you can get what you want., like this:

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft]

Hi, @neelofarshama 

According to your description and sample data, I can roughly understand your requirement, I think you can achieve this using calculated columns, you can try these calculated columns:

Term = DATEDIFF([Start Date],[ End date],MONTH)+1

Term Revenue = DIVIDE([Expected Revenue],[Term])

Current =

var _month=MONTH(TODAY())

return

IF(

    MONTH([Start Date])<=_month&&MONTH([ End date])>=_month,[Term Revenue],

    BLANK())

Next 1 month =

var _eomonth=EOMONTH(TODAY(),1)

return

IF(

    [Start Date]<=_eomonth&&EOMONTH([ End date],0)>=_eomonth,[Term Revenue],

    BLANK())

Next 2 month =

var _eomonth=EOMONTH(TODAY(),2)

return

IF(

    [Start Date]<=_eomonth&&EOMONTH([ End date],0)>=_eomonth,[Term Revenue],

    BLANK())

Next 3 month =

var _eomonth=EOMONTH(TODAY(),3)

return

IF(

    [Start Date]<=_eomonth&&EOMONTH([ End date],0)>=_eomonth,[Term Revenue],

    BLANK())

Next 4 month =

var _eomonth=EOMONTH(TODAY(),4)

return

IF(

    [Start Date]<=_eomonth&&EOMONTH([ End date],0)>=_eomonth,[Term Revenue],

    BLANK())

Next 5 month =

var _eomonth=EOMONTH(TODAY(),5)

return

IF(

    [Start Date]<=_eomonth&&EOMONTH([ End date],0)>=_eomonth,[Term Revenue],

    BLANK())

Next 6 month =

var _eomonth=EOMONTH(TODAY(),6)

return

IF(

    [Start Date]<=_eomonth&&EOMONTH([ End date],0)>=_eomonth,[Term Revenue],

    BLANK())

And you can get what you want, like this:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[neelofarshama]

Hi @v-robertq-msft ,

Thank you for the reply. I have tried your DAX for the report I am getting results but not as expected,

in the screnshot below I have start dates in the year 2020 and 2019 for which current month and +6 months columns should not be displayed but they are getting displayed. The seconf highlighted row is reflecting $48.75 it should reflect $97.50.

The other issue is for present year start date 1/7/2021(Jan) the revenue should be displayed for May(Current Month) and June(Next 1 month) but its displaying for all 6 months.

For other column start date is 2/11/2021 the revenue should be displayed for May(Current Month), June(Next 1 month), July(Next 2 months) but its not displaying for current month.

I am attaching the link of the pbix file below, please help me on this

https://caltimes-my.sharepoint.com/:u:/g/personal/neelofer_shama_latimes_com/EXeUVsAOVXNOoRVfjJE6pfU...

Thanks.