Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Experts,
I have daxed the below to be able to get the sum of the gross values where the latest submission date is picked for each application ID and summed with the others. This seems to pick for me only the gross value with the latest date based on all applications. The desire value needs to be 15,214.13 and right now it is showing me 1,982.33
I need to have the value in a visual card as a KPI . Not concerned with a slicer or anything. Unfortunately, I am not able to attach the powerBi file but I am attaching a snip of the Page visuals.
Solved! Go to Solution.
Hi, @kalkhudary
You can try the following methods.
Max Date =
CALCULATE(MAX('Table'[Submission Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),[statuscode] in {"In Review", "In Review - Manager", "Submitted","Approved", "Opted Out"}))
Measure =
Var _table=SUMMARIZE('Table','Table'[ID],'Table'[statuscode],"Max Date",[Max Date],"Sum",CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[ID]),[Submission Date]=[Max Date])))
Return
SUMX(_table,[Sum])
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks so much. That worked perfect!
The interesting part is that the dax logic is applying to looking at the lastest submission date for the applicationid but when it is summing the gross value, it does sum based on the max submisison date of all the application IDs (9/11/2023). Any thoughts abotu this dilema.
Hi, @kalkhudary
You can try the following methods.
Max Date =
CALCULATE(MAX('Table'[Submission Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),[statuscode] in {"In Review", "In Review - Manager", "Submitted","Approved", "Opted Out"}))
Measure =
Var _table=SUMMARIZE('Table','Table'[ID],'Table'[statuscode],"Max Date",[Max Date],"Sum",CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[ID]),[Submission Date]=[Max Date])))
Return
SUMX(_table,[Sum])
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |