Hi Michael,

You need to use Power Pivot for this. I see you haven't started the course yet, but you will need to get through to the Time Intelligence section as your DAX measure for the running total will be like this:

Running Total:=

CALCULATE(

      SUM([Daily G/L $]), 

      FILTER( 

           ALL('Calendar'[Date]),

           'Calendar'[Date] <= MAX('Calendar'[Date])

     )

)

And the measure for the % Change that adapts to the different level of time granularity will need several measures, as explained here.

Mynda

Mynda,

Thank you for the response!  I did take the Time Intelligence course today.  Must I also create a calendar table here? If I give up the granularity and simply have a the %change on an annual basis only does that simplify the solution?  I'm afraid I don't think I am yet skilled enough to implement the solutions as your have presented.

Mike

Hi Mike,

Yes, giving up the ability to drill down to lower date levels of granularity simplifies the solution significantly.

You need a date table any time you want to use the Time Intelligence DAX functions anyway. In the attached file you'll see the measures don't use time intelligence functions, but they still refer to fields in the date table. I recommend you always use a date table.

Hope that points you in the right direction. Best to finish the course before going too far down the rabbit hole with your own file. It's not fun having to start from scratch because you missed something fundamental.

Mynda

Mynda,

Thank you, this works perfectly to what I was hoping!

How would the formula for "YOY % Change of Running Total" differ if I wanted to divide the current year net profit by the running total up to the end of the previous year?  IE: (Sum Daily G/L in 2018)/(Running total to end of 2017)?

Mike

Hi Michael,

To clarify, at March 2018 you'd want to do this:

=March YTD 2018/March YTD 2017 or

=March YTD 2018/Dec YTD 2017 ?

Mynda

Mynda,

I was able to resolve this objective with the information available.  Thank you for the response though!

Mike

Great! Glad you figured it out 🙂