How to carry forward the data to next month until new data is available?

[AKath_12]

Hi, 

I have struggled with the DAX formula and hope an expert here could help. 

I have a data set that inputs on an approximately yearly basis. However, in my display result, I would like the latest data to be carried forward to the next month until a new data value is available. 

For example, the data input from November 2021 and onwards should display 2.38, and the next data available is September 2022 and onwards should display 2.54. 

Note: I have a date table which actively linked to one of my data sets. However, the average result is pulled from another dataset, which has an inactive relationship with the date table. Not sure if this is relevant, hope this provides more information about the solution. Many thanks!

[dharmendars007 · Accepted solution]

Hello @AKath_12 , 

Since you mentioned that the "average result" is pulled from a dataset with an inactive relationship to the date table, make sure that you activate this relationship within your DAX query when needed. You can use USERRELATIONSHOT() to activate the inactive relationship.

Carry Forward Result =
VAR LastValue =
CALCULATE(
MAX('YourDataTable'[Result]),USERELATIONSHIP('YourDateTable'[Date], 'YourDataTable'[Date]),
FILTER(ALL('YourDataTable'),'YourDataTable'[Date] <= MAX('YourDateTable'[Date])))
RETURN
LastValue

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[AKath_12]

Hi, 

I just realized the solution given is not quite right. The data shows the Max result value instead of providing the latest result value. I have generated a file as below:-

@dharmendars007 @SachinNandanwar, could you please help and relook into the solution? Thank you very much!

https://drive.google.com/file/d/1fR9R1Yl2Zs1NaE5zeBWEt5Hn8X08MZy-/view?usp=drive_link

[AKath_12]

Thank you both @SachinNandanwar , @dharmendars007  for resolving the issue. the script provided earlier is very helpful. I didn't get it earlier due to the relationship between my calendar and the dataset. I have finally got the correct value after fixing it.=)

[SachinNandanwar · Accepted solution]

I have used a sample data that has Date in the same table as the values.

Sample Data >>

and used the following measure to get the expected output.

MEASURE =
VAR _Curr_Date =
    MAX ( '1'[Date] )
VAR _SummarizeVar =
    CALCULATE (
        MAX ( '1'[Date].[Date] ),
        FILTER ( ALL ( '1' ), [Date] < _Curr_Date && [Value] > 0 )
    )
VAR _LatestValue =
    LOOKUPVALUE ( '1'[Value], '1'[Date], _SummarizeVar )
RETURN
    IF (
        SELECTEDVALUE ( '1'[Value], 0.00 ) = 0,
        _LatestValue,
        SELECTEDVALUE ( '1'[Value] )
    )

You can adjust the above measure to include the relationship across the Date table.

Output >>

[AKath_12]

Thank you very much @SachinNandanwar and @dharmendars007 

I tried to combine both DAX formulas, however, the result was not as expected. So I changed it to the formula below instead, it turns out gave me consecutive results for each month, however, it auto-average the result and the month displayed did not seem quite right.

CALCULATE('Scores by Questions'[Average Score this year],

    USERELATIONSHIP('Date'[Date],'All'[Start Date]),

    FILTER(ALL('Date'),'Date'[Date]<= MAX('Date'[Date])))

2.54 should have been displayed from November onwards, however, the result showed 2.46, and displayed wrongly from Sept onwards instead.

The same goes for result 3.44 should be displayed from Dec 2023 onwards, however, 2.71 displayed in June 2023 instead.

The indirect relationship that I have used is Many to one and single cross-filter direction. 

Looking forward to more of your advice! Thank you!

[SachinNandanwar]

Please share the data.

[AKath_12]

Hi Sachin, sorry for my late reply. I'm not sure how to attach the file here. Do you have an email so I can send it to you? 

The raw data is as below:-

Number	Question	Scores	Max Scores	Start Time	End Time	Average Scores
1	M	2	6	9/30/2018 20:32	11/13/2018 20:33	2
1	S	2	6	9/30/2018 20:32	11/13/2018 20:33	2
1	C	3	6	9/30/2018 20:32	11/13/2018 20:33	3
1	P	4	6	9/30/2018 20:32	11/13/2018 20:33	4
1	T	4	6	9/30/2018 20:32	11/13/2018 20:33	4
1	P	4	6	9/30/2018 20:32	11/13/2018 20:33	4
1	C	2	6	9/30/2018 20:32	11/13/2018 20:33	2
1	E	4	6	9/30/2018 20:32	11/13/2018 20:33	4
1	S	3	6	9/30/2018 20:32	11/13/2018 20:33	3
1	B	2	6	9/30/2018 20:32	11/13/2018 20:33	2
1	G	2	6	9/30/2018 20:32	11/13/2018 20:33	2
2	M	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	S	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	C	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	C	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	P	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	T	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	S	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	P	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	C	4	6	6/10/2019 1:00	6/12/2019 9:00	4
2	D	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	E	3	6	6/10/2019 1:00	6/12/2019 9:00	3
2	S	2	6	6/10/2019 1:00	6/12/2019 9:00	2
2	P	3	6	6/10/2019 1:00	6/12/2019 9:00	3
3	M	2	6	11/25/2020 0:00	11/27/2020 10:00	2
3	S	2	6	11/25/2020 0:00	11/27/2020 10:00	2
3	C	3	6	11/25/2020 0:00	11/27/2020 10:00	3
3	C	2	6	11/25/2020 0:00	11/27/2020 10:00	2
3	P	4	6	11/25/2020 0:00	11/27/2020 10:00	4
3	S	2	6	11/25/2020 0:00	11/27/2020 10:00	2
3	C	2	6	11/25/2020 0:00	11/27/2020 10:00	2
3	C	2	6	11/25/2020 0:00	11/27/2020 10:00	2
3	10 : /	4	6	11/25/2020 0:00	11/27/2020 10:00	4
3	E	2	6	11/25/2020 0:00	11/27/2020 10:00	2
3	S	2	6	11/25/2020 0:00	11/27/2020 10:00	2
3	D	4	6	11/25/2020 0:00	11/27/2020 10:00	4
3	P	4	6	11/25/2020 0:00	11/27/2020 10:00	4
4	<	2	6	12/21/2021 13:00	12/22/2021 23:00	2
4	C	2	6	12/21/2021 13:00	12/22/2021 23:00	2
4	P	2	6	12/21/2021 13:00	12/22/2021 23:00	2
4	S	2	6	12/21/2021 13:00	12/22/2021 23:00	2
4	C	2	6	12/21/2021 13:00	12/22/2021 23:00	2
4	C	2	6	12/21/2021 13:00	12/22/2021 23:00	2
4	E	2	6	12/21/2021 13:00	12/22/2021 23:00	2
4	S	2	6	12/21/2021 13:00	12/22/2021 23:00	2
4	G	4	6	12/21/2021 13:00	12/22/2021 23:00	4
5	M	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	D	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	C	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	C	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	P	4	6	9/1/2022 12:00	9/22/2022 8:30	4
5	T	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	D	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	C	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	C	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	S	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	R	2	6	9/1/2022 12:00	9/22/2022 8:30	2
5	F	3	6	9/1/2022 12:00	9/22/2022 8:30	3
5	F	2	6	9/1/2022 12:00	9/22/2022 8:30	2
6	G	2	6	11/20/2023 12:00	11/21/2023 21:00	2
6	S	2	6	11/20/2023 12:00	11/21/2023 21:00	2
6	S	2	6	11/20/2023 12:00	11/21/2023 21:00	2
6	S	2	6	11/20/2023 12:00	11/21/2023 21:00	2
6	S	4	6	11/20/2023 12:00	11/21/2023 21:00	4
6	G	3	6	11/20/2023 12:00	11/21/2023 21:00	3
6	C	2	6	11/20/2023 12:00	11/21/2023 21:00	2
6	C	2	6	11/20/2023 12:00	11/21/2023 21:00	2
6	C	2	6	11/20/2023 12:00	11/21/2023 21:00	2
6	C	2	6	11/20/2023 12:00	11/21/2023 21:00	2
6	E	3	6	11/20/2023 12:00	11/21/2023 21:00	3
6	A	4	6	11/20/2023 12:00	11/21/2023 21:00	4
6	D	1	6	11/20/2023 12:00	11/21/2023 21:00	1

[dharmendars007 · Accepted solution]

Hello @AKath_12 , 

Since you mentioned that the "average result" is pulled from a dataset with an inactive relationship to the date table, make sure that you activate this relationship within your DAX query when needed. You can use USERRELATIONSHOT() to activate the inactive relationship.

Carry Forward Result =
VAR LastValue =
CALCULATE(
MAX('YourDataTable'[Result]),USERELATIONSHIP('YourDateTable'[Date], 'YourDataTable'[Date]),
FILTER(ALL('YourDataTable'),'YourDataTable'[Date] <= MAX('YourDateTable'[Date])))
RETURN
LastValue

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN