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.
Please help me for cumulaitve value for Particular month like January to May
Solved! Go to Solution.
I am surprised to see this post still open. Did you try to use my solution? It is a straightforward cumulative DAX pattern with one additional statement to account for month of May logic you want. Give it a try.
RT Jan to May = CALCULATE ( [Sales Amount], FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) && MAX ( 'Date'[Date] ) <= DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 4, 30 ) && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) )
Hello Martin,
Thanks for your replay, I have tried by the following formula
SimpleCumulative:=CALCULATE(SUM(Sales[SalesAmount]),FILTER(ALL('Calendar'[EnglishMonthName]), 'Calendar'[EnglishMonthName]<=MAX('Calendar'[EnglishMonthName])),VALUES('Calendar'[Year]))
The formula giving the result of all month but i need only the result from January to May Month?
Hi @arifulice
It turned out to be a pretty tricky question even though it appears to be an easy one.
Looking at the code below you will immediately recognize the standard DAX for calculating running totals. However, it's the bolded part that helps to retun the logic that you want.
What we say here is that the max dates in the filter context must be less or equal to the last day of the month of May of the year in the current filter context.
RT Jan to May = CALCULATE ( [Sales Amount], FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) && MAX ( 'Date'[Date] ) <= DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 5, 31 ) && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) )
Thanks, Nick
Below my code :
SimpleCumulative:=CALCULATE(SUM(Sales[SalesAmount]),FILTER(ALL('Calendar'), 'Calendar'[EnglishMonthName]<=MAX('Calendar'[EnglishMonthName])))
The result of the code is below
But i want to get result only first four months..
I noticed your months are sorted alphabetically and not by month number. This has to be done in the first place otherwise none of your measures will work properly.
Also, you mentioned that now you want to return 4 months of running total. To get that reflected with my code, you need to change the month number and a date as shown below in the part marked in bold.
RT Jan to May = CALCULATE ( [Sales Amount], FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) && MAX ( 'Date'[Date] ) <= DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 4, 30 ) && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) )
Without manual entry i want to get my 2010(Jan to Mar) month sales datat.
Is possiable can we do in power bi ?
If I understood you correctly you are looking to see your sales only from Jan 2010 to Mar 2010.
Sales Jan To Mar = CALCULATE ( [Sales Amount], FILTER ( Date, Date[Date] >= DATE(2010, 1,1) && Date[Date] <= DATE(2010, 3, 31) ) )
Nick -
Ya right . I don t want 2010,1,1 bez every month i am updateing the dashboard i don t like to modify other thing so i need everything by a function .
For example Now i have April 2010 dataset . for your reference again i need to go query and modify that then only it s working .
bez beforely we write the query between Jan 2010 to Mar 2010.
But i don t want do again and again .
Did you got my questions?
Thanks
First of all. Your new question is not relative to the topic of this forum post. Could you please start a new forum post and let's start understanding what is your goal in a new post. Please tag my name @nickchobotar so i know what's posted. I would like to help you with this.
If possible, please show the result you expect to get visually in a table.
N -
Try this out then:
Make a column which determines what number of month you are in. Then go for this DAX formula
SimpleCumulative:=IF('Calender'[EnglishMonthNameNUMBER]=<4;CALCULATE(SUM(Sales[SalesAmount]),FILTER(ALL('Calendar'), 'Calendar'[EnglishMonthName]<=MAX('Calendar'[EnglishMonthName])));0)
Let me know if it makes sense and if it works.
Hi ValubiMartin ,
I have tried by your given formila but it not working properly giving error
My formula
SimpleCumulative:=IF('Calendar'[MonthNumber]<=4,CALCULATE(SUM(Sales[SalesAmount]),FILTER(ALL('Calendar'), 'Calendar'[MonthNumber]<=MAX('Calendar'[MonthNumber]))),0)
Can you post the error message when using ValubiMartin's formula? Besides, nickchobotar's solution should also work, have you ever try it?
Regards,
Charlie Liao
Hi v-caliao-msft,
The Error when using ValubiMartin formula are given below
"MonthNumber in table calendar can not be determined.This can be happen when a measure formula refers to a column that contains many values without specifying an aggegation such as min, max,count....."
Thanks
ArifulIce
I am surprised to see this post still open. Did you try to use my solution? It is a straightforward cumulative DAX pattern with one additional statement to account for month of May logic you want. Give it a try.
RT Jan to May = CALCULATE ( [Sales Amount], FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) && MAX ( 'Date'[Date] ) <= DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 4, 30 ) && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) )
Hi @nickchobotar,
I'm using the below DAX formula for Cumulative count
Actual Cumulative = SUMX(FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])),[Actual Delivery Date])
But, I required cumulative count till current month (Date) not for future months (Dates).
Can u please help me out
Thanks.
Thanks nickchobotar for your help. .. I have tried to solve my problem by using your formula witout sorting month.After sorting the problem is solved
Very good!!!
The solution developed by @nickchobotar and the one developed by me are very different, and they both depend on how your data is stored and what conditions you want to impose on the calculation. So if you could elaborate on why you need to make the calculation based on certain months, it would be really helpful.
Hi @arifulice
Can you show me the visual, where you are plotting your data?
I've solved a similar problem before, where the solution was this:
Cumulative total of Value = CALCULATE(SUM('Table'[Value]);FILTER(ALL('Table'[Date]); 'Table'[Date] <= MAX ( 'Table'[Date] )))
This assumes that your date column is of type date. Below you can see a picture of my solution. Let me know how it goes for you.
If this doesn't work out for you i need to know more about how your data is stored.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |