Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
nhuda
Helper I
Helper I

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

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 selectionSlider selectionTable dataTable 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])
	)
)

 

  

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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])

vyangliumsft_0-1713851928560.png

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])

vyangliumsft_1-1713851928564.png

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])

vyangliumsft_2-1713851962875.png

 

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

View solution in original post

7 REPLIES 7
nhuda
Helper I
Helper I

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
Community Support
Community Support

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])

vyangliumsft_0-1713851928560.png

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])

vyangliumsft_1-1713851928564.png

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])

vyangliumsft_2-1713851962875.png

 

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

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

ProjectProjectStartDateMaterialGroupTestedDateTested
Project X8/1/2021A8/1/20210
Project X8/1/2021A8/1/20210
Project X8/1/2021A9/17/20211
Project X8/1/2021A9/17/20211
Project X8/1/2021A9/17/20211
Project X8/1/2021A9/17/20211
Project X8/1/2021A10/26/20211
Project X8/1/2021A10/26/20211
Project X8/1/2021A12/10/20211
Project X8/1/2021A12/10/20211
Project X8/1/2021A7/25/20221
Project X8/1/2021A7/25/20221
Project X8/1/2021A7/25/20221
Project X8/1/2021A7/25/20221
Project X8/1/2021A7/25/20221
Project X8/1/2021A7/25/20221
Project X8/1/2021A7/25/20221
Project X8/1/2021A7/28/20221
Project X8/1/2021A7/28/20221
Project X8/1/2021A7/28/20221
Project X8/1/2021A7/29/20221
Project X8/1/2021A7/29/20221
Project X8/1/2021A7/29/20221
Project X8/1/2021A7/29/20221
Project X8/1/2021A7/29/20221
Project X8/1/2021A7/29/20221
Project X8/1/2021A7/29/20221
Project X8/1/2021A7/29/20221
Project X8/1/2021A7/31/20221
Project X8/1/2021A7/31/20221
Project X8/1/2021A7/31/20221
Project X8/1/2021A7/31/20221
Project X8/1/2021A7/31/20221
Project X8/1/2021A7/31/20221
Project X8/1/2021A7/31/20221

Thank you,

nhuda

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

Thanks for your responses.

This is now resolved thanks to Liu.

Later, Regards,

nhuda

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.