Forum Discussion

VendettaBob's avatar
VendettaBob
Icon for Helper II rankHelper II
4 years ago
Solved

Running Total for a given start and end date

I would like to put 2 running totals for this year and the previous year on the same chart.

My calc for the running total is:

 

CALCULATE (
    [Is SHEQ],
    FILTER (
        ALLSELECTED('EventYearMonth'[DateValue]),
        'EventYearMonth'[DateValue] <= MAX ( 'EventYearMonth'[DateValue])
     && MAX('EventYearMonth'[DateValue]) <= TODAY()  )

 

Rather than having a relative slicer on the visual with "this year". I would like to include it within the calculation. This way I can overlay the previous and current years.

 

Is this possible?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  VendettaBob ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    Measure =
IF(
    YEAR(MAX('Table'[date]))=YEAR(TODAY()),
    CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]<=TODAY()&&'Table'[date]>=DATE(YEAR(TODAY()),1,1))),
    IF(
        YEAR(MAX('Table'[date]))=YEAR(TODAY())-1 ,
     CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),
        'Table'[date]>=DATE(YEAR(TODAY())-1,1,1)&&'Table'[date]<=DATE(YEAR(TODAY()),12,31)))))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    VendettaBob , Create a measure like

     

    var _max1 = MAX ( 'EventYearMonth'[DateValue])
    var _max = date(year(_max1), month(_max1)-12, day(_max1))
    var _today = date(year(TODAY()), month(TODAY())-12, day(TODAY()))
    return
    CALCULATE (
    [Is SHEQ],
    FILTER (
    ALLSELECTED('EventYearMonth'[DateValue]),
    'EventYearMonth'[DateValue] <= _max
    && MAX('EventYearMonth'[DateValue]) <= _today ) )

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  VendettaBob ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    Measure =
IF(
    YEAR(MAX('Table'[date]))=YEAR(TODAY()),
    CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]<=TODAY()&&'Table'[date]>=DATE(YEAR(TODAY()),1,1))),
    IF(
        YEAR(MAX('Table'[date]))=YEAR(TODAY())-1 ,
     CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),
        'Table'[date]>=DATE(YEAR(TODAY())-1,1,1)&&'Table'[date]<=DATE(YEAR(TODAY()),12,31)))))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly