Running Total DAX exclude last years totals

[Anonymous]

Hello

I am running in to a bit of trouble on my Running Total formula. I want it to calculate the running total YTD but for some reason it is including last year (2017) in the sum for January 2018. 

[Anonymous]

When you use ALL(Calendar[Date]), you remove the filter context on the current year.  So it returns every date that is less than the max date.

Try this:

RunningTotal =
CALCULATE (
    SUM ( 'Labor Data'[Hrs] ),
    FILTER (
        ALL ( Calendar[Date] ),
        Calendar[Date] <= MAX ( Calendar[Date] )
            && Calendar[Year] = MAX ( Calendar[Year] )
    )
)

Now it is keeping a filter context on the current year.  So no 2017 data will show up in the 2018 portion of the table.

[Anonymous]

Appreciate the response. That does make sense however, I receive an error saying "A single value for column 'Year' in table 'Calendar' cannot be determined. I believe it is due to my 'Year' field containing many values as it is dervied from the 'Date' field.

[Anonymous · Accepted solution]

Hard to tell exactly what's going on without seeing what type of visual you're trying to build. Can you include a screenshot of what fields you're putting on a visual? Also, are any slicers active? We need to see what the initial filter context is to help troubleshoot this further.

Also, if you're using a standard calendar (not a fiscal or 445 calendar), you can also try the built in time intelligence function:

[RunningTotal] =
CALCULATE ( SUM ( 'Labor Data'[Hrs] ), DATESYTD ( Calendar[Date] ) )

[Anonymous]

Thank you kindly, I'll go with the time intelligence function.

[Anonymous]

Hello

I am running in to a bit of trouble on my Running Total formula. I want it to calculate the running total YTD but for some reason it is including last year (2017) in the sum for January 2018.

I have two tables (Calendar and Labor Data). The Calendar table includes Date, Year, MonthNumber, and MonthPrefix fields. Below is my formula. Any thoughts?

RunningTotal = CALCULATE(Sum('Labor Data'[Hrs]),FILTER(ALL(Calendar[Date]),Calendar[Date] <= Max(Calendar[Date])))

[v-jianhe-msft]

Hi,

To calculate the running total YTD, you could try with the expression

Running Total= Calculate(Sum(‘Labor Data’[Hrs]),DATESYTD(Calendar[Date],”31/12”))

You could refer to this example Calculating Sales Financial Year to Date in Power BI with DAX

A similar thread: DAX - Running Total YTD

Best Regards,

Henry

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(Sum('Labor Data'[Hrs]),DATESYTD(Calendar[Date],"31/12"))

Drag Month from the Calendar table into your visual.  In the slicer/filter, select the year as 2018.  There should be a relationship from the Date column of the Lasor Data Table to the Date column of the Calendar Table.

Hope this helps.