Adding rows using DAX expression based on condition

[kkalyanrr]

Hello Team ,

I'm trying to project forecast for the coming months based on end date , if the difference amount is >2000 I'm splitting in to next coming months. My requirement is to add a new Forecast Date column and add rows of coming months.
How can I acheive this.
Here is the dataset , forecast date is custom column I've created..

I'm expecting output like the attached pic.

[Mariusz · Accepted solution]

HI @kkalyanrr 

You can create a table like below, or see the attached for reference.

Table 2 = 
VAR __tbl = 'Table'
VAR __date = TODAY()
VAR __forecastDate1 =
    DATE( YEAR( __date ), MONTH( __date ), 1 )  
VAR __forecastDate2 =
    DATE( YEAR( __date ), MONTH( __date ) +1, 1 )  
VAR __forecastDate3 =
    DATE( YEAR( __date ), MONTH( __date ) +2, 1 ) 
VAR __baseTbl = ADDCOLUMNS( __tbl, "Forecast Date", __forecastDate1 )
VAR __forecastDateTbl = { __forecastDate2, __forecastDate3 }
VAR __filterTbl = 
    FILTER( __tbl, 'Table'[Acc Total] > 2000 )
RETURN 
    UNION(
        __baseTbl,
        CROSSJOIN( __filterTbl, __forecastDateTbl )
    )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Icey · Accepted solution]

Hi @kkalyanrr ,

Modify @Mariusz 's expression like so:

Table 2 = 
VAR __tbl = 'Table'
VAR __date = TODAY()
VAR __forecastDate1 =
    DATE( YEAR( __date ), MONTH( __date ), 1 )  
VAR __forecastDate2 =
    DATE( YEAR( __date ), MONTH( __date ) +1, 1 )  
VAR __forecastDate3 =
    DATE( YEAR( __date ), MONTH( __date ) +2, 1 ) 
VAR __baseTbl = ADDCOLUMNS( __tbl, "Forecast Date", __forecastDate1 )
VAR __forecastDateTbl = { __forecastDate2, __forecastDate3 }
VAR __filterTbl = 
    FILTER( __tbl, 'Table'[Difference] > 2000 )   ----------use 'Table'[Difference] instead of 'Table'[Acc Total]
RETURN 
    UNION(
        __baseTbl,
        CROSSJOIN( __filterTbl, __forecastDateTbl )
    )

Please let us know if this works.

Best Regards,

Icey

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

[Mariusz]

Hi @kkalyanrr 

Can you paste the sample as a data table rather than a screenshot?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[kkalyanrr]

@Mariusz  - how can I post excel file here, please suggest.

[Mariusz]

Hi @kkalyanrr 

You can select a table array from excel, copy and paste it here in the body of the message, hope this helps.

A	B	C
1	2	3

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[kkalyanrr]

Thank you @Mariusz  

Job Number	Azzz Amount	Acc Total	Difference	EndDate
43973	8953.79	790.9	8162.89	22-05-2020
44012	19662.89	1058.2	18604.69	30-06-2020
43997	25160.08	2085.4	23074.68	15-06-2020
44001	6525.37	1253.4	5271.97	19-06-2020
43986	38519.94	1727.78	36792.16	04-06-2020
43980	11502.84	698	10804.84	29-05-2020
44001	14210.5	498	13712.50	19-06-2020
43997	21147.63	1807.79	19339.84	15-06-2020
43988	6016.16	1626.62	4389.54	06-06-2020
44001	5200.66	1654.75	3545.91	19-06-2020
43959	18703.78	1336.55	17367.23	08-05-2020
43980	13161.95	1354.15	11807.80	29-05-2020
43987	30213.98	892.1	29321.88	05-06-2020
43994	13672.26	1242.7	12429.56	12-06-2020
43980	6112.23	854.15	5258.08	29-05-2020
44001	35420.63	894.74	34525.89	19-06-2020
44015	15035.17	1208.51	13826.66	03-07-2020
44008	21378.43	942.7	20435.73	26-06-2020
44001	3008.08	198	2810.08	19-06-2020
43980	11993.06	1466.65	10526.41	29-05-2020
44008	10412.51	741.95	9670.56	26-06-2020
43995	7428.58	0	7428.58	13-06-2020
43994	9244.16	1198	8046.16	12-06-2020
44011	21136.42	498	20638.42	29-06-2020
44001	21657.2	600	21057.20	19-06-2020
43992	13848.24	300	13548.24	10-06-2020
44008	22573.71	800	21773.71	26-06-2020
44001	23556.71	961.8	22594.91	19-06-2020
43994	9430.7	554.95	8875.75	12-06-2020
43903	43202.79	43202.79	0.00	13-03-2020
43967	202705.57	202500.38	205.19	16-05-2020

[Mariusz · Accepted solution]

HI @kkalyanrr 

You can create a table like below, or see the attached for reference.

Table 2 = 
VAR __tbl = 'Table'
VAR __date = TODAY()
VAR __forecastDate1 =
    DATE( YEAR( __date ), MONTH( __date ), 1 )  
VAR __forecastDate2 =
    DATE( YEAR( __date ), MONTH( __date ) +1, 1 )  
VAR __forecastDate3 =
    DATE( YEAR( __date ), MONTH( __date ) +2, 1 ) 
VAR __baseTbl = ADDCOLUMNS( __tbl, "Forecast Date", __forecastDate1 )
VAR __forecastDateTbl = { __forecastDate2, __forecastDate3 }
VAR __filterTbl = 
    FILTER( __tbl, 'Table'[Acc Total] > 2000 )
RETURN 
    UNION(
        __baseTbl,
        CROSSJOIN( __filterTbl, __forecastDateTbl )
    )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn