How to get the data that are not selected using the slider date range for cumulative value

[nhuda]

Hello,

I have a table-based report where there are 4 rows of data, 1 for each for groups A, B, C and D. there are total for the period columns and there are cumulative columns. I am having hard time calculating the cumulative columns. It should capture the data for the period selected in the Slider (tested date), but it should also capture data from the beginning (available) to the "from date" selected in the slider (pic attached for understanding). There's no issue getting the data for the range selected in Slider, but I am not able to get the other part of the data that are not selected using date range so I can add it to have cumulative.Slider selectionTable data

Any help would be appreciated. BTW, I tried the following below where testeddatecopy is the copy of the testeddate column. Because it's used in the Slider, so I tried to separate it from the selected range. ProjectStartDate is from which date data might be present: 

Cumulative Tested = 
VAR fromDate = MIN('vw_WeldRepairStats'[TestedDate])
VAR toDate =
    MAX ('vw_WeldRepairStats'[TestedDate])
VAR pStartDate = MIN('vw_WeldRepairStats'[ProjectStartDate])
VAR dataNotSelected = CALCULATE(
	[Total Tested],
	FILTER(
		ALLSELECTED('vw_WeldRepairStats'), 
		'vw_WeldRepairStats'[TestedDateCopy] <= fromDate &&
        'vw_WeldRepairStats'[TestedDateCopy] >= pStartDate &&
        'vw_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_WeldRepairStats'[MaterialGroup])
	)
)
RETURN
dataNotSelected +
CALCULATE(
	[Total Tested],
	FILTER(
		ALLSELECTED('vw_WeldRepairStats'), 
		'vw_WeldRepairStats'[TestedDate] <= toDate &&
        'vw_WeldRepairStats'[TestedDate] >= fromDate &&
        'vw_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_WeldRepairStats'[MaterialGroup])
	)
)

[nhuda]

Hi @v-yangliu-msft ,

I am encountering 1 issue with this formula. So when there's data for the period selected in date slider, the cumulative calculation works fine, but when data for the period is none (no data), then the cumulative is coming up as blank as well, even though the cumulative should have data.

Any idea how this can be resolved/

Thank you,

nhuda

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @Ashish_Mathur , please allow me to provide another insight:

Hi  @nhuda ,

If you want to get dates that have not been filtered before, you can define the dates before performing the calculation.

For example:

I want to get the smallest date that has not been filtered after grouping by [Project], I can use the All() function. The All() function ignores any filters that may have been applied, so it won't be affected by the slicer (date 2021-8-1).

MINX(FILTER(ALL('vw_WeldRepairStats'),'vw_WeldRepairStats'[Project]=_select),[ProjectStartDate])

ALL function (DAX) - DAX | Microsoft Learn

I want to get the maximum date selected in the slicer, I can use the allselect() function (the slicer selects the maximum date as 2022-6-30, if there is no corresponding 2022-6-30, it will select the maximum date less than 2022-6-30 after: 2021-12-10).

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. 

MAXX(ALLSELECTED('vw_WeldRepairStats'),'vw_WeldRepairStats'[TestedDate])

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Create measure.

Measure =
var _select=SELECTEDVALUE('vw_WeldRepairStats'[Project])
var _pStartDate=MINX(FILTER(ALL('vw_WeldRepairStats'),'vw_WeldRepairStats'[Project]=_select),[ProjectStartDate])
var _fromDate=MAXX(ALLSELECTED('vw_WeldRepairStats'),'vw_WeldRepairStats'[TestedDate])
return
SUMX(
    FILTER(ALL('vw_WeldRepairStats'),
    'vw_WeldRepairStats'[TestedDate]>=_pStartDate&&'vw_WeldRepairStats'[TestedDate]<=_fromDate&&'vw_WeldRepairStats'[Project]=_select),[Tested])

Best Regards,

Liu Yang

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

[nhuda]

Hi Liu,

Thanks very much for the info provided.

I tried it and it did work, just had to specify the MatGroup logic as well.

Regards,

nhuda

[Ashish_Mathur]

Hi,

I cannot understand your question.  When you have selected a certain period, then why should the accumulatiion start from a date not selected?  Also, share data in a format that can be pasted in an MS Excel file.  In a simple Table format, show the expected result very clearly.

[nhuda]

Hi Ashish,

Here's some data added. If you look at it and say filter by MatGroup A, there are some tested numbers for 9/17/2021. So when period selected is for example from October 1, 2021 to June 30, 2022 - Tested for the period is 4, but cumulative for the period is 8 (including the September 2021 numbers). It is the way cumulative should work in this case right. 

So in essence, changing "From Date" in the slider should not change the cumulative, changing "To Date" can change the cumulative when there are additional data.

That's why the accumulation is always from the beginning (project start date).

Please let me know if you have any additional questions.

Project	ProjectStartDate	MaterialGroup	TestedDate	Tested
Project X	8/1/2021	A	8/1/2021	0
Project X	8/1/2021	A	8/1/2021	0
Project X	8/1/2021	A	9/17/2021	1
Project X	8/1/2021	A	9/17/2021	1
Project X	8/1/2021	A	9/17/2021	1
Project X	8/1/2021	A	9/17/2021	1
Project X	8/1/2021	A	10/26/2021	1
Project X	8/1/2021	A	10/26/2021	1
Project X	8/1/2021	A	12/10/2021	1
Project X	8/1/2021	A	12/10/2021	1
Project X	8/1/2021	A	7/25/2022	1
Project X	8/1/2021	A	7/25/2022	1
Project X	8/1/2021	A	7/25/2022	1
Project X	8/1/2021	A	7/25/2022	1
Project X	8/1/2021	A	7/25/2022	1
Project X	8/1/2021	A	7/25/2022	1
Project X	8/1/2021	A	7/25/2022	1
Project X	8/1/2021	A	7/28/2022	1
Project X	8/1/2021	A	7/28/2022	1
Project X	8/1/2021	A	7/28/2022	1
Project X	8/1/2021	A	7/29/2022	1
Project X	8/1/2021	A	7/29/2022	1
Project X	8/1/2021	A	7/29/2022	1
Project X	8/1/2021	A	7/29/2022	1
Project X	8/1/2021	A	7/29/2022	1
Project X	8/1/2021	A	7/29/2022	1
Project X	8/1/2021	A	7/29/2022	1
Project X	8/1/2021	A	7/29/2022	1
Project X	8/1/2021	A	7/31/2022	1
Project X	8/1/2021	A	7/31/2022	1
Project X	8/1/2021	A	7/31/2022	1
Project X	8/1/2021	A	7/31/2022	1
Project X	8/1/2021	A	7/31/2022	1
Project X	8/1/2021	A	7/31/2022	1
Project X	8/1/2021	A	7/31/2022	1

Thank you,

nhuda

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result.

[nhuda]

Hi Ashish,

Thanks for your responses.

This is now resolved thanks to Liu.

Later, Regards,

nhuda