Need Help with Project Types

[royalty123]

Hi, I have two types of projects 1. Agile 2. Software. The below formulas apply to both the Project types. Initially our projects took 3 months to complete but lately it was taking 6 months so I wanted to show the 3 months data in 6 months and took an expert's help from the community. But the issue now I'm facing is I want to show the data from 3 months to 6 months for Agile projects only not for Software projects since they take 3 months to complete, so the old formula (3 months formula) works fine for Software projects. Could anyone please suggest if there is any DAX formula or IF condition that we can apply to show 6 months formula for Agile and 3 months formula for Software. For Agile projects we take 60 Hours and for Software projects we take 6 hrs to complete

FYI - I need to show both Agile and Software projects types in the same stacked column chart so I cannot add two different tables in PowerBI and need one combined formula to suffice both the needs. 

Sample table for your reference

Id

Title

Supply type per_

Projects

Status

LeadTime

HoursTotal

Date

hoursweek

hourspending1

nextmonth1

hourspending2

nextmonth2

workhours1

workhours2

workhours3

Date Completed

1

A

Stragetic

Agile

active

12

60

1/9/2024

5

40

2/9/2024 0:00

20

3/9/2024 0:00

10

10

10

2

B

Operational

Software

active

1

6

2/15/2024

1

3

3/15/2024

3

4/15/2024

3

3

0

3 months Formula:

WORKS = UNION(

    SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[Date].[Date])),WORKSMONTH[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects],"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[Date].[Date],

                    IF(WORKSMONTH[HoursTotal]>0,

                        IF(WORKSMONTH[HoursTotal]<=WORKSMONTH[hoursweek]*4,WORKSMONTH[HoursTotal],WORKSMONTH[hoursweek]*4), 

                    BLANK()),

                BLANK())),

    SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[nextmonth1].[Date])),WORKSMONTH[nextmonth1].[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects],"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[nextmonth1].[Date],

                    IF(WORKSMONTH[hourspending1]>0,

                        IF(WORKSMONTH[hourspending1]<=WORKSMONTH[hoursweek]*4,WORKSMONTH[hourspending1],WORKSMONTH[hoursweek]*4),

                    BLANK()),

                BLANK())),

    SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[nextmonth2].[Date])),WORKSMONTH[nextmonth2].[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects],"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[nextmonth2].[Date],

                    IF(WORKSMONTH[hourspending2]>0,

                        IF(WORKSMONTH[hourspending2]<=WORKSMONTH[hoursweek]*4,WORKSMONTH[hourspending2],WORKSMONTH[hoursweek]*4),

                    BLANK()),

                BLANK()))

    )

6 months Formula after expert's advise I added the same formula for next 3 months:

WORKS = UNION(

    SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[Date].[Date])),WORKSMONTH[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects,"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[Date].[Date],

                    IF(WORKSMONTH[HoursTotal]>0,

                        IF(WORKSMONTH[HoursTotal]<=WORKSMONTH[hoursweek]*2,WORKSMONTH[HoursTotal],WORKSMONTH[hoursweek]*2),

                    BLANK()),

                BLANK())),

    SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[nextmonth1].[Date])),WORKSMONTH[nextmonth1].[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects,"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[nextmonth1].[Date],

                    IF(WORKSMONTH[hourspending1]>0,

                        IF(WORKSMONTH[hourspending1]<=WORKSMONTH[hoursweek]*2,WORKSMONTH[hourspending1],WORKSMONTH[hoursweek]*2),

                    BLANK()),

                BLANK())),

    SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[nextmonth2].[Date])),WORKSMONTH[nextmonth2].[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects,"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[nextmonth2].[Date],

                    IF(WORKSMONTH[hourspending2]>0,

                        IF(WORKSMONTH[hourspending2]<=WORKSMONTH[hoursweek]*2,WORKSMONTH[hourspending2],WORKSMONTH[hoursweek]*2),

                    BLANK()),

                BLANK())),

    SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[nextmonth3].[Date])),WORKSMONTH[nextmonth3].[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects,"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[nextmonth3].[Date],

                    IF(WORKSMONTH[hourspending3]>0,

                        IF(WORKSMONTH[hourspending3]<=WORKSMONTH[hoursweek]*2,WORKSMONTH[hourspending3],WORKSMONTH[hoursweek]*2),

                    BLANK()),

                BLANK())),

   SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[nextmonth4].[Date])),WORKSMONTH[nextmonth4].[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects,"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[nextmonth4].[Date],

                    IF(WORKSMONTH[hourspending4]>0,

                        IF(WORKSMONTH[hourspending4]<=WORKSMONTH[hoursweek]*2,WORKSMONTH[hourspending4],WORKSMONTH[hoursweek]*2),

                    BLANK()),

                BLANK())),

    SELECTCOLUMNS(WORKSMONTH,"m",IF(NOT(ISBLANK(WORKSMONTH[nextmonth5].[Date])),WORKSMONTH[nextmonth5].[Date],BLANK()),

        "ID",WORKSMONTH[Id],"Title",WORKSMONTH[Title],"Status",WORKSMONTH[Status],"ProjectType",WORKSMONTH[Projects,"Supply",WORKSMONTH[Supply type per_],

        "value",IF(WORKSMONTH[nextmonth4].[Date],

                    IF(WORKSMONTH[hourspending5]>0,

                        IF(WORKSMONTH[hourspending5]<=WORKSMONTH[hoursweek]*2,WORKSMONTH[hourspending5],WORKSMONTH[hoursweek]*2),

                    BLANK()))

Thanks in advance!!

@lbendlin @Ashish_Mathur @amitchandak @Greg_Deckler @hackcrr @Emily_Kim @jdbuchanan71 @Ritaf1983 @ryan_mayu @danextian @parry2k

[johnbasha33]

@royalty123  we can work it out with a dynamic calculation.

check and let us know if it works.

calculated column: 
LeadTime =
IF ( Projects[ProjectType] = "Agile", 12, 3 )

use this in the visual.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!