Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to calculate to do the product calculation to each row, but I can only get it to calculate from the beginning of dataset instead of calculating based on the slicer date. As you can see in the first picture and the second picture. In the second picture after the slicer is applied, it should start calculating from the beginning again and the cumulative result should be 94.8%.
Thank you for your help.
Solved! Go to Solution.
Hi @dannytango ,
According to my understand, you want to calculate the accumulate of measure dynamically based on Slicer, right?
You could follow these steps:
1. Create a new table with Date for Slicer.
2. Use the following formula:
test =
VAR _min =
MIN ( 'forSlicer'[Date] )
VAR _max =
MAX ( 'forSlicer'[Date] )
RETURN
CALCULATE (
PRODUCTX ( TCMMonthlyReturnsInput, TCMMonthlyReturnsInput[MRTCM] + 1 ),
FILTER (
TCMMonthlyReturnsInput,
'TCMMonthlyReturnsInput'[Date] >= _min
&& 'TCMMonthlyReturnsInput'[Date] <= MAX ( 'TCMMonthlyReturnsInput'[Date] )
)
) - 1
3. Apply the measure below to filter pane( set =1)
Measure =
VAR _min =
MIN ( 'forSlicer'[Date] )
VAR _max =
MAX ( 'forSlicer'[Date] )
RETURN
IF (
MAX ( 'TCMMonthlyReturnsInput'[Date] ) >= _min
&& MAX ( 'TCMMonthlyReturnsInput'[Date] ) <= _max,
1,
0
)
4. Calculate the accumulate value:
Final Accumulate test =
CALCULATE (
SUMX ( 'TCMMonthlyReturnsInput', [test] ),
FILTER (
ALL ( 'TCMMonthlyReturnsInput' ),
'TCMMonthlyReturnsInput'[Date] <= MAX ( 'TCMMonthlyReturnsInput'[Date] )
&& [Measure] = 1
)
)
My visualization looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @dannytango ,
According to my understand, you want to calculate the accumulate of measure dynamically based on Slicer, right?
You could follow these steps:
1. Create a new table with Date for Slicer.
2. Use the following formula:
test =
VAR _min =
MIN ( 'forSlicer'[Date] )
VAR _max =
MAX ( 'forSlicer'[Date] )
RETURN
CALCULATE (
PRODUCTX ( TCMMonthlyReturnsInput, TCMMonthlyReturnsInput[MRTCM] + 1 ),
FILTER (
TCMMonthlyReturnsInput,
'TCMMonthlyReturnsInput'[Date] >= _min
&& 'TCMMonthlyReturnsInput'[Date] <= MAX ( 'TCMMonthlyReturnsInput'[Date] )
)
) - 1
3. Apply the measure below to filter pane( set =1)
Measure =
VAR _min =
MIN ( 'forSlicer'[Date] )
VAR _max =
MAX ( 'forSlicer'[Date] )
RETURN
IF (
MAX ( 'TCMMonthlyReturnsInput'[Date] ) >= _min
&& MAX ( 'TCMMonthlyReturnsInput'[Date] ) <= _max,
1,
0
)
4. Calculate the accumulate value:
Final Accumulate test =
CALCULATE (
SUMX ( 'TCMMonthlyReturnsInput', [test] ),
FILTER (
ALL ( 'TCMMonthlyReturnsInput' ),
'TCMMonthlyReturnsInput'[Date] <= MAX ( 'TCMMonthlyReturnsInput'[Date] )
&& [Measure] = 1
)
)
My visualization looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
The code returned the calculation total correctly, but doesn't calculate for each row. Couldn't upload pbix file, so I have to put paste it into table below. Thanks.
Date | MRTCM |
1/31/2017 | 0.66% |
2/28/2017 | 0.21% |
3/31/2017 | 0.96% |
4/30/2017 | 0.66% |
5/31/2017 | 5.21% |
6/30/2017 | 4.20% |
7/31/2017 | 9.14% |
8/31/2017 | 8.62% |
9/30/2017 | 3.12% |
10/31/2017 | 14.91% |
11/30/2017 | 3.09% |
12/31/2017 | -0.26% |
1/31/2018 | 11.04% |
2/28/2018 | 4.16% |
3/31/2018 | -7.44% |
4/30/2018 | -2.48% |
5/31/2018 | 24.14% |
6/30/2018 | 0.99% |
7/31/2018 | -0.97% |
8/31/2018 | 8.98% |
9/30/2018 | 0.84% |
10/31/2018 | -16.97% |
11/30/2018 | -7.69% |
12/31/2018 | -31.07% |
1/31/2019 | 23.02% |
2/28/2019 | 0.24% |
3/31/2019 | 8.83% |
4/30/2019 | 10.56% |
5/31/2019 | -23.15% |
6/30/2019 | 29.09% |
7/31/2019 | 10.17% |
8/31/2019 | -7.13% |
9/30/2019 | 2.98% |
10/31/2019 | 8.71% |
11/30/2019 | 3.65% |
12/31/2019 | 3.76% |
1/31/2020 | 7.39% |
2/29/2020 | 0.98% |
3/31/2020 | -3.48% |
4/30/2020 | 24.12% |
5/31/2020 | 8.34% |
6/30/2020 | 7.90% |
7/31/2020 | 6.49% |
8/31/2020 | 3.00% |
9/30/2020 | 0.01% |
Thanks, @AllisonKennedy
Sorry, but I'm pretty new to DAX so I don't think I've applied your advice properly. This is my attempt following your advice:
Try updating to something like this;
TCMCumulative test =
VAR startDate =
CALCULATE (
MIN ( TCMMonthlyReturnsInput[Date] ),
ALLSELECTED ( TCMMonthlyReturnsInput[Date] )
)
RETURN
CALCULATE (
PRODUCTX (
FILTER ( TCMMonthlyReturnsInput, startdate <= TCMMonthlyReturnsInput[Date] ),
TCMMonthlyReturnsInput[MRTCM] + 1
),
ALLSELECTED ( TCMMonthlyReturnsInput[Date], TCMMonthlyReturnsInput[MRTCM] )
) - 1
If that still doesn't work, can you please provide sample data?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com