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

 JanFebMarAprMay
A1584024
B158404246

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! 

 
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.

8 Replies

  • 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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

      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.

       

      Productjanfebmaraprmayjunjul
      Trousers (Current)200040003000500600800100

      Trousers (Desired)

      		2000400030003500410049005000
              

       

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    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.

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

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

     

     

    Best Regards,

    Icey