Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I need to find the cumulative sum of my sales but my sales are not a direct field in the table. Its a measure created by me:-
Netsales =((CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]>=445000,VW_RPT_SWGLINTD[NTOFNO]<=445999))+(CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]=449000))+(CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]=450000))+(CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]>=448100,VW_RPT_SWGLINTD[NTOFNO]<=448999))+(CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]>=451000,VW_RPT_SWGLINTD[NTOFNO]<=451999)))
Now I want to use this Net sales measure to get the cumulative sales. I tried:-
Running Net sales = calculate([Netsales]),filter(all(VW_RPT_SWGLINTD),VW_RPT_SWGLINTD[Date Picker <=max(VW_RPT_SWGLINTD[Date Picker])))
But it doesn't work. It just gave the same value as the NetSales. I cannot take Sum([Netsales]) as Netsales is a measure and it gives an error. I need some way to find the sum of a measure that I created. I also tried using the calculated column but it doesn't work either.
Solved! Go to Solution.
Hi !
You can get the desired out using following DAX measure;
Running Net Sales =
CALCULATE(
[Netsales],
FILTER(
ALLSELECTED('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
)
)
You can replace the 'Calendar' dimension name with your date table in the DAX formula.
Regards,
Hasham
Hi !
You can get the desired out using following DAX measure;
Running Net Sales =
CALCULATE(
[Netsales],
FILTER(
ALLSELECTED('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
)
)
You can replace the 'Calendar' dimension name with your date table in the DAX formula.
Regards,
Hasham
Hii Hasham,
I tried your solution. It didn't work. I created this:-
Hi !
Try to use the VW_RPT_SWGLINTD[Date Picker] column in your matrix.
Regards,
Hii,
I was using VW_RPT_SWGLINTD[Date Picker] as a hierarchy. When I used it as a single dimension. It worked. thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |