Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I'm having sales data for months by products.I want to calclulate cumulative of sales for N months ,if the months have positive sales.Month Selection is dynamic.In my case i'm considering for 3 months.
Solved! Go to Solution.
Hi @Likhitha ,
We can create a meausre as below.
Measure =
VAR ed =
EDATE ( MAX ( 'Current Year'[Date] ), -3 )
VAR d =
MAX ( 'Current Year'[Date] )
RETURN
CALCULATE (
SUM ( 'Current Year'[Total Customer] ),
FILTER (
ALLSELECTED ( 'Current Year' ),
'Current Year'[Date] > ed
&& 'Current Year'[Date] <= d
&& 'Current Year'[Total Customer] >= 0
)
)
Hello All,
Thank you so much for your solutions.
I created some DAX and got the solution.
Hi @Likhitha ,
We can create a meausre as below.
Measure =
VAR ed =
EDATE ( MAX ( 'Current Year'[Date] ), -3 )
VAR d =
MAX ( 'Current Year'[Date] )
RETURN
CALCULATE (
SUM ( 'Current Year'[Total Customer] ),
FILTER (
ALLSELECTED ( 'Current Year' ),
'Current Year'[Date] > ed
&& 'Current Year'[Date] <= d
&& 'Current Year'[Total Customer] >= 0
)
)
Hi @Likhitha ,
Use the below measure to your calculation:
Cumulative = CALCULATE([Total],FILTER(ALLSELECTED(Table1),Table1[Month Num] <= MAX(Table1[Month Num])))
Don't forget to hit Thumbs up and accept this as a solution if you find it helpful!
@Tahreem24 In my case , if i select december month the cumulative sum should happen only if my november and october months have positive values,otherwise the product should be ignored.(I'm doing cumulative for 3 months).
Attached screen shot for your reference:
Please give KUDOS for support! 😊
Please try below measure:
@Tahreem24 In that DAX,how can i check that each month sales value is positive only??
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |