cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neelofarshama
Post Partisan
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

neelofarshama_0-1620411750833.png

 

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 RevenueStart Date End dateTermTerm RevenueCurrent+6months     
     MayJuneJulyAugustSeptemberOctoberNovember
150007/1/20219/30/202135000 $       -   $       -   $     5,000 $     5,000 $     5,000 $         -   $         -  
22,50010/1/20226/30/202292500 $       -   $       -   $         -   $         -   $         -   $     2,500 $     2,500
200012/1/20204/30/20215400 $       -   $       -   $       -   $       -   $       -   $       -   $       -  
25,0005/16/202112/19/202183,125 $       3,125 $       3,125 $       3,125 $       3,125 $       3,125 $       3,125 $       3,125
7,0104/1/202111/5/20218876.19 $      876.19 $      876.19 $      876.19 $      876.19 $      876.19 $      876.19 $      876.19
1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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”:

v-robertq-msft_0-1620801994114.png

 

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

v-robertq-msft_1-1620801994118.png

v-robertq-msft_2-1620801994167.png

 

 

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.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

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”:

v-robertq-msft_0-1620801994114.png

 

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

v-robertq-msft_1-1620801994118.png

v-robertq-msft_2-1620801994167.png

 

 

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.

View solution in original post

v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1620640126646.png

 

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.

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.

neelofarshama_0-1620719484180.png

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.

neelofarshama_1-1620719712369.png

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors