Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a measure which calculates cumulative loss over development years (which is also a measure based on start year and performance month). I used the following the DAX to calculate AnnualDevelopmenDisplay (calcualted from Annual Development) and the CumulativeNetCollateralLoss.
DimDeal.StartDate | DealID | PerformanceMonth | CreditEventNetLossAmount |
2/26/2014 | 32 | 3/1/2014 | 0 |
2/26/2014 | 32 | 4/1/2014 | 0 |
2/26/2014 | 32 | 5/1/2014 | 0 |
2/26/2014 | 32 | 6/1/2014 | 0 |
2/26/2014 | 32 | 7/1/2014 | 61806 |
2/26/2014 | 32 | 8/1/2014 | 12534 |
2/26/2014 | 32 | 9/1/2014 | 93461 |
2/26/2014 | 32 | 10/1/2014 | 174623 |
2/26/2014 | 32 | 11/1/2014 | 119235 |
2/26/2014 | 32 | 12/1/2014 | 177913 |
2/26/2014 | 32 | 1/1/2015 | 81489 |
2/26/2014 | 32 | 2/1/2015 | 249500 |
2/26/2014 | 32 | 3/1/2015 | 88451 |
2/26/2014 | 32 | 4/1/2015 | 116480 |
2/26/2014 | 32 | 5/1/2015 | 91149 |
2/26/2014 | 32 | 6/1/2015 | 223681 |
2/26/2014 | 32 | 7/1/2015 | 20611 |
2/26/2014 | 32 | 8/1/2015 | 107788 |
2/26/2014 | 32 | 9/1/2015 | 219877 |
2/26/2014 | 32 | 10/1/2015 | 65134 |
2/26/2014 | 32 | 11/1/2015 | 176428 |
2/26/2014 | 32 | 12/1/2015 | 194564 |
2/26/2014 | 32 | 1/1/2016 | 276480 |
2/26/2014 | 32 | 2/1/2016 | 205944 |
2/26/2014 | 32 | 3/1/2016 | 370500 |
2/26/2014 | 32 | 4/1/2016 | 51543 |
2/26/2014 | 32 | 5/1/2016 | 169476 |
2/26/2014 | 32 | 6/1/2016 | 268503 |
2/26/2014 | 32 | 7/1/2016 | 328455 |
2/26/2014 | 32 | 8/1/2016 | 216249 |
2/26/2014 | 32 | 9/1/2016 | 186896 |
2/26/2014 | 32 | 10/1/2016 | 279587 |
2/26/2014 | 32 | 11/1/2016 | 220184 |
2/26/2014 | 32 | 12/1/2016 | 203915 |
2/26/2014 | 32 | 1/1/2017 | 150670 |
2/26/2014 | 32 | 2/1/2017 | 344587 |
2/26/2014 | 32 | 3/1/2017 | 443104 |
2/26/2014 | 32 | 4/1/2017 | 176915 |
2/26/2014 | 32 | 5/1/2017 | 195982 |
2/26/2014 | 32 | 6/1/2017 | 155957 |
2/26/2014 | 32 | 7/1/2017 | 130947 |
2/26/2014 | 32 | 8/1/2017 | 253935 |
2/26/2014 | 32 | 9/1/2017 | 167044 |
2/26/2014 | 32 | 10/1/2017 | 271076 |
2/26/2014 | 32 | 11/1/2017 | 239677 |
2/26/2014 | 32 | 12/1/2017 | 190364 |
2/26/2014 | 32 | 1/1/2018 | 258930 |
2/26/2014 | 32 | 2/1/2018 | 138391 |
2/26/2014 | 32 | 3/1/2018 | 162087 |
2/26/2014 | 32 | 4/1/2018 | 475329 |
2/26/2014 | 32 | 5/1/2018 | 513233 |
2/26/2014 | 32 | 6/1/2018 | 173594 |
2/26/2014 | 32 | 7/1/2018 | 261316 |
2/26/2014 | 32 | 8/1/2018 | 182793 |
2/26/2014 | 32 | 9/1/2018 | 117470 |
2/26/2014 | 32 | 10/1/2018 | 117017 |
2/26/2014 | 32 | 11/1/2018 | 261685 |
2/26/2014 | 32 | 12/1/2018 | 153336 |
Solved! Go to Solution.
hi, @Anonymous
I have use this formula to tested in directquery, it works well
CALCULATE(SUM('Import for sample Market Risk'[CreditEventNetLossAmount]),FILTER(ALL('Import for sample Market Risk'),'Import for sample Market Risk'[DevelopmentAnnualDisplay]>=MIN('Import for sample Market Risk'[DevelopmentAnnualDisplay])))or
CALCULATE(SUM('Import for sample Market Risk'[CreditEventNetLossAmount]),FILTER(ALL('Import for sample Market Risk'[DevelopmentAnnualDisplay]),'Import for sample Market Risk'[DevelopmentAnnualDisplay]>=MIN('Import for sample Market Risk'[DevelopmentAnnualDisplay])))
Best Regards,
Lin
hi, @Anonymous
Just use ALL instead of ALLSELECTED for the cumulative total measure.
Measure = CALCULATE ( SUM ( FactCRTLoanPerformanceMonth[CreditEventNetLossAmount] ), FILTER ( ALL ( FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay] ), ISONORAFTER ( FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay], MAX ( FactCRTLoanPerformanceMonth[AnnualDevelopmentDisplay] ), DESC ) ) )
Result:
Best Regards,
Lin
Thank you very much for replying, I tried using ALL as you suggested below. Unfortunately, it's still not working in my case. I am attaching the screenshots here for results with the measure as per your instruction and my previous measure.
Please let me kow if there is any other workaround to get that cumulative number at the end of the each year (cumulative value carried forward to the next year from the previous year). I have tried to use one more measure (CumulativeLosses, DAX below) which is based on perfromance month not the annualDevelopmentDisplay. It shows the the right cumulative values but my next step would be to just get the cumulative value at the end of each year (December values, as highlighted in the image below).
hi, @Anonymous
1. when you use all function, the result should not be affected by all(...), are that attribute column is from different table, could you share your sample pbix file?
2. If you need to add a year condition in your measure.
CumulativeLosses = CALCULATE ( SUM ( FactCRTLoanPerformanceMonth[CreditEventNetLossAmount] ), FILTER ( ALL ( FactCRTLoanPerformanceMonth[PerformanceMonth] ), FactCRTLoanPerformanceMonth[PerformanceMonth] <= MAX ( FactCRTLoanPerformanceMonth[PerformanceMonth] ) && FactCRTLoanPerformanceMonth[PerformanceYear] = MAX ( FactCRTLoanPerformanceMonth[PerformanceYear] ) ) )
If not your case, please share your sample pbix file and your expected output for use have a test.
Best Regards,
Lin
Thank you very much for your reply, I have created a sample pbix and used ALL function and IT WORKED!
But the issue is NOT RESOLVED in DirectQuery mode. I use the same exact formula which i used in sample file but somehow things are not working in Direct Query mode. How do I investigate this issue and see what are the factors affecting the result. I will attach my sample file here and also the screenshots from the direct query mode.
Please let me know if you think something is affecting the result in the direct query mode.
Thank you very much for your time.
VidyaSample File
hi, @Anonymous
The file doesn’t exist, please share it again.
Do you mean that the same formula in the same data model, when it is import it works and when it is directquery it doesn't work?
there are some limitations in dax function when using directquery, what is your formula?
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery
Best Regards,
Lin
https://www.dropbox.com/s/4acsbhbmko1wh1m/Sample%20for%20PowerBICommunity.pbix?dl=0
Please find the sample file.
Formula used in sample file:
hi, @Anonymous
I have use this formula to tested in directquery, it works well
CALCULATE(SUM('Import for sample Market Risk'[CreditEventNetLossAmount]),FILTER(ALL('Import for sample Market Risk'),'Import for sample Market Risk'[DevelopmentAnnualDisplay]>=MIN('Import for sample Market Risk'[DevelopmentAnnualDisplay])))or
CALCULATE(SUM('Import for sample Market Risk'[CreditEventNetLossAmount]),FILTER(ALL('Import for sample Market Risk'[DevelopmentAnnualDisplay]),'Import for sample Market Risk'[DevelopmentAnnualDisplay]>=MIN('Import for sample Market Risk'[DevelopmentAnnualDisplay])))
Best Regards,
Lin
Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |