Total to date working the same way as TOTALYTD

[Matjo]

Is there a formula that works like total year to date, but instead of year to date it sums everything until the date. 

I have tried to create a running sum by using DAX but it does not behave as I want. TOTALYTD does exactly what I want except I want it for all previous dates instead of this year.  

Here is an example FILE. For January 2017 I would like the running diff to be -100 for company A and 0 for B in February I would like it to be -200 for A and 100 for B

[v-huizhn-msft · Accepted solution]

Hi @Matjo,

I download your file, and create a Calendar table using the formula by clicking "New Table" under Modeling on Home page.

Calendar = CALENDAR(MIN(Test[Date]),MAX(Test[Date]))

Create relationship between Calendar and Test table.

Then create a measure using the formula below.

running_diff =
CALCULATE (
    Test[diff],
    FILTER ( ALL ( Calendar ), Calendar[Date] <= MAX ( Calendar[Date] ) )
)

Please select the Test[Company] as rows, Calendar[Date] as column, you will get expected result as follows.


Best Regards,
Angelia

[Matjo]

Hi @v-huizhn-msft and thanks!

I got it to work in my big model now. I had been playing around with similar solutions but never got them correct and they were very slow. I then discovered that Power BI had created the crossfilter between my date dimension and one of the fact tables running both ways and when I made that single everything started working. 

Thanks for your time!

Best regards,
Mattias