Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 selection
Table 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])
)
)
Solved! Go to 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
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
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
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
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.
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
Hi,
Based on the data that you have shared, show the expected result.
Hi Ashish,
Thanks for your responses.
This is now resolved thanks to Liu.
Later, Regards,
nhuda