Dax running total after certain Month

[Anonymous]

Hi Guys,

Please could you help with a calculation i need. 

I want to calculate Row B. Row B turns into a rolling total after March

	Jan	Feb	Mar	Apr	May
A	15	8	40	2	4
B	15	8	40	42	46

Row A is Sum(Sales)

Is there a Dax formula that will allow me to do this? 

Would be helpful to have a hard code formula. And a dynamic formula which calcs a running total after the current month 

Thank you in advance! 

[Icey]

Hi @Anonymous ,

Please let me know if the workaround I posted above could meet your requirements.

Best Regards,

Icey

[Icey · Accepted solution]

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.

[mahoneypat]

I put your data in and used query to transpose it and make a Date column with the start of each month.  Here is the M code as an example.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSUfJKzAOSbqlJQNI3sQhIOhYUgdmVSrE60UqOQLahKZCwAGITAyBhBGKA5Zww5EzAkmZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{" ", type text}, {"A", Int64.Type}, {"B", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{" ", "Month"}, {"A", "Sales"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"B"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Date", each Date.FromText("2020-"&[Month]&"-01"), type date),
#"Inserted Month" = Table.AddColumn(#"Added Custom", "Month.1", each Date.Month([Date]), Int64.Type)
in
#"Inserted Month"

Once you do that, you can use these two DAX measures to calculate your fixed and dynamic running totals:

Mar Running Total =
VAR selecteddate =
SELECTEDVALUE ( RT[Date] )
RETURN
IF (
selecteddate >= DATE ( 2020, 3, 1 ),
CALCULATE (
SUM ( RT[Sales] ),
FILTER (
ALL ( RT ),
AND ( RT[Date] >= DATE ( 2020, 3, 1 ), RT[Date] <= selecteddate )
)
),
SUM ( RT[Sales] )
)

Dynamic Running Total =

VAR selecteddate =

SELECTEDVALUE(RT[Date])

var startcurrentmonth = Date(Year(TODAY()), MONTH(TODAY()), 1)

RETURN

IF (

selecteddate >= startcurrentmonth,

CALCULATE (

SUM ( RT[Sales] ),

FILTER (

ALL ( RT ),

AND ( RT[Date] >= startcurrentmonth, RT[Date] <= selecteddate )

)

),

SUM ( RT[Sales] )

)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Apologies Pat,

I forgot to mention i have a matrix chart and there is a product Catergory on the Rows & Date in the Columns.

Your calc is very close, and is giving me the correct total but when used with the product field there is some duplication

Could you advise?

Aaron

[mahoneypat]

Assuming your Sales table is many:1 with each of the Product and Date tables (and that you transformed the Sales table and made a Date column for the relationship) - 

Dynamic Running Total =

VAR selecteddate =

SELECTEDVALUE('Date'[Date])

var startcurrentmonth = Date(Year(TODAY()), MONTH(TODAY()), 1)

RETURN

IF (

selecteddate >= startcurrentmonth,

CALCULATE (

SUM ( RT[Sales] ),

FILTER (

ALL ( 'Date' ),

AND ( 'Date'[Date] >= startcurrentmonth, 'Date'[Date] <= selecteddate )

)

),

SUM ( RT[Sales] )

)

Hard w/o directly trying it on the model, but I think that will work.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi Pat,

That still doesnt work. I have 2 tables in the model

Sales Table

calendar Table

The product field is already in the Sales Table.

You calc is doing the same as the sum and there is no running total after a certain month. Could you advise?

[Greg_Deckler]

@Anonymous - 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. 

[Anonymous]

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