Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dax running total after certain Month

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 ...
data protection
  • Icey's avatar
    Icey
    6 years ago

    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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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?