Rolling 6months on Gantt chart

[Anonymous]

Hi

Is it possible to get solution for my requirement in power bi? I tried in all the ways still not able to find solution so seeking expert suggestions.

I am using Gantt chart (imported) and have columns Start Date and end date, product name, activity name. I have start and end date in my slicers as well. When I select start date as 1/1/2020 my Gantt chart has to show from selected date to 6months ex Jan 2020 to June 2020. If user selects end date as 1/8/2020 then Gantt chart has to show Mar2020 to Aug2020. If user selects between range that range only has to show and default start date is current date and end date is 6months.

If user selecting more than 6months then have to show popup message or error message saying that pls select 6months.

I don't have any measure here. And most important thing is start and end date both are coming from same table. I tried with disconnected table but everyone used measure in the existing posts which I got from Google.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Try to redefine the start date and end date based on the slicer value to determine the range.

__start date = 
VAR x = SELECTEDVALUE(Sheet4[End Date])
VAR __year = YEAR(x)
VAR __month = MONTH(x)-5
VAR __day = DAY(x)
RETURN
IF(
    __month < 0,
    DATE( __year-1, __month+12,__day),
    DATE( __year, __month, __day)
)

__end date = 
VAR x = SELECTEDVALUE(Sheet4[Start Date])
VAR __year = YEAR(x)
VAR __month = MONTH(x)+5
VAR __day = DAY(x)
RETURN
IF(
    __month > 12,
    DATE( __year+1, __month-12,__day),
    DATE( __year, __month, __day)
)

Best regards,
Lionel Chen

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

[Anonymous]

@v-lionel-msft . Awesome.. Great its working.. Thank you so much. I have tried another but user liked your approach.. Kudoos

Thanks,

Venkat

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.

[Anonymous]

@Ashish_Mathur thanks for your suggestions but i need it in DAX.

[Ashish_Mathur]

Hi,

Mine is a DAX solution.  DAX is the formula language of the PowerPivot.  PowerPivot is available in both MS Excel and PowerBI Desktop.

[Anonymous]

Apologies how can i see the code in excel which you provided. I am not sure how to check and i have opened your excel, showing me Input,Bridge tables and Output. Where can i see your dax formula? 

Thanks,

Venkat

[Ashish_Mathur]

Hi,

Go to PowerPivot > Measures > Manage.

[Anonymous]

@Ashish_Mathur can't see your code. Clicked on your link which you provided and below message showed in the bottom "You may download my solution workbook from here." i have downloaded and can't see power pivot option. Please provide some instructions how to see your dax in the link

Thanks,

Venkat

[Ashish_Mathur]

Hi,

So either your version does not support the PowerPivot or if it does, then it is not activated.  To activate it, go to File > Options > AddIns > Manage > COM-AddIns > Go.  Check the box of PowerPivot and click on OK.  It should appear in the ribbon.  Now click on PowerPivot > Measures > Manage Measures.

[Anonymous]

@Ashish_Mathur Hi Ashish, Can't see Addin option in your excel. Is that possible to past your dax code or to share pbix file.?

If anyone has done this kind of requirement kindly post your suggestions. As iam newbie not able to get to achieve this solution

Thanks,

Venkat

[Ashish_Mathur]

Hi,

May be your version of MS Excel does not support the PowerPivot.  Download my file and keep it closed.  Open PowerBI Desktop and go to File > Import > Power Query, PowerPivot and PowerView.  This will import all relationships and formulas into PowerBI Desktop.

[amitchandak]

@Anonymous , Refer to this video if you select how to get a trend for more than that date. This requires an additional table

https://www.youtube.com/watch?v=duMSovyosXE

[Anonymous]

@amitchandak Hi thanks for your reply. But this video not matching with my requirement he has been used measure but I don't have measure in my requirement. Can anyone has done this kind of requirement kindly let me know. I have tried all the possibilities from Google and YouTube then finally coming to you.

[Anonymous]

Can anyone give your suggestions please? Seems like we need to write some complex Dax to achieve but I have very minimal experience into power bi that's seeking help