cancel
Showing results for
Did you mean:
Post Partisan

## DAX for upcoming 6 months from current month

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
1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Community Support

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:

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

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.

Post Partisan

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.

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

Thanks.

Announcements