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.

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?