Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi
I have a table as following:
Date | No of Days | Type |
12-Nov-2020 | 12 | A |
30-Jan-2021 | 5 | B |
4-Apr-2020 | 8 | C |
5-May-2021 | 2 | S |
6-Aug-2021 | 22 | A |
Now, I need to sort the date column and calculate the sum of days before Type S from Type column. I have tried but I am not getting how to write a DAX. Any h
Solved! Go to Solution.
Hi, @kamalbandam
Try to create a measure like this:
Measure =
var _date_S=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Type]="S"))
var _sum=CALCULATE(SUM('Table'[No of Days]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Date]<_date_S&&'Table'[Date]<=MAX('Table'[Date])))
return _sum
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @kamalbandam
Try to create a measure like this:
Measure =
var _date_S=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Type]="S"))
var _sum=CALCULATE(SUM('Table'[No of Days]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Date]<_date_S&&'Table'[Date]<=MAX('Table'[Date])))
return _sum
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @NidhiBhusari
Thanks for answering but I have some other rows and also where I need to take for a particular record of a person as shown below:
Date | No of Days | Type | Name |
12-Nov-2020 | 12 | A | Mark |
30-Jan-2021 | 5 | B | Mark |
4-Apr-2020 | 8 | C | Mark |
5-May-2021 | 2 | S | Mark |
6-Aug-2021 | 22 | A | Mark |
31-Dec-2020 | 21 | C | John |
26-Jan-2021 | 15 | S | John |
3-Mar-2021 | 4 | A | John |
Now, I need to get the result as Mark - 25 No of days & John - 21 No of days.
Sorry for the incomplete info.
Thanks in Advance
@kamalbandam Do you still want to use this condition - Now, I need to sort the date column and calculate the sum of days before Type S from Type column. I have tried but I am not getting how to write a DAX.
@kamalbandam I have assigned ID to each row and then created two measures as shown in below attached screenshots.
but it is static where you keep the index number as you know it but we need to do it dynamically
User | Count |
---|---|
85 | |
84 | |
71 | |
65 | |
57 |
User | Count |
---|---|
129 | |
102 | |
91 | |
83 | |
67 |