Forum Discussion
Dax running total after certain Month
Hi Anonymous ,
Try to create a measure like so:
Measure = IF ( MAX ( 'Calendar'[MonthNo.] ) > 3, CALCULATE ( SUM ( Sales[Sales] ), FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[MonthNo.] <= MAX ( 'Calendar'[MonthNo.] ) && 'Calendar'[MonthNo.] >= 3 ) ), SUM ( Sales[Sales] ) )BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionAnonymous - Well, I am going to make the assumption that there is a date field involved somewhere in all of this. If that is the case, you could do this:
Cumulative Sum =
VAR __AB = MAX([A B Thingy Column])
VAR __CurrentDate = MAX([Date])
VAR __MinDate = DATE(2020,3,1)
RETURN
IF(__CurrentDate < __MinDate || __AB = "A",
SUM([Value]),
SUMX(FILTER(ALL('Table'),[Date] >= __MinDate && [Date] <= __CurrentDate),[Value])
)
There is an entire boat load of assumptions in there.
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Apologies I havent posted in a while.. I didnt get the desired from your previous formula 😞
So I am trying to calculate the running total after a certain month. But its a little complicated. The months after March are forecasted and need to be running totals from the previous month. So April should be 3500 for Trousers from the example below. However in the raw data it is actually 500. Is there a way to manipulate the data like this without affecting the prior months?
-Sales Field
-Date Field
-Product Field
I do have a date field and is linked to a calendar.
Product jan feb mar apr may jun jul Trousers (Current) 2000 4000 3000 500 600 800 100 Trousers (Desired)
2000 4000 3000 3500 4100 4900 5000