Running Total Over Multiple Years

Question

I have an Orders fact table and a connected Date dimension.

I'd like to create a running total measure by year and month, but for the current year I only want it to total up to the current date and then be blank afterwards. I'll be turning it into a bar graph and I don't want the plateau that will happen as it calculates each month that hasn't happened yet as the current YTD value.

I'm coming from a SQL background where this would be an easy task, but seems difficult in power bi/dax.

I'm currently working with the following dax:

SalesRT_NEW =
IF (
    MONTH ( TODAY () ) <= MAX ( DimDate[Date] ),
    TOTALYTD ( SUM ( 'FactOrderDetails(3yr)'[LineTotal] ), DimDate[Date] ),
    BLANK ()
)

Output (as table for testing) is (starting from June for brevities sake):

YEAR  Month  SalesRT
2017  Jun    1500
2018  Jun    1750
2019  Jun    1900
2017  Jul    1650
2018  Jul    1858
2019  Jul    2050
2017  Aug    1800
2018  Aug    1965
2019  Aug    2050

Desired output:

YEAR  Month  SalesRT
2017  Jun    1500
2018  Jun    1750
2019  Jun    1900
2017  Jul    1650
2018  Jul    1858
2019  Jul    2050
2017  Aug    1800
2018  Aug    1965
2019  Aug

MPJ567 MPJ567 · Accepted Answer · 2019-07-12T18:42:56

Got the answer:

IF(MAX(DimDate[Date]) <= today() || MONTH(MAX(DimDate[Date])) = Month(today()),TOTALYTD([SalesTotal], DimDate[Date]), BLANK())

Running Total Over Multiple Years

1 Answers