DAX expression TotalYTD results in blanks

Question

I'm using an expression

=TOTALYTD([Distance Cycling],'Calendar'[Date])

Distance cycling is a calculated measure from a table of records of exercise

=CALCULATE([Sum of Distance (m or km], Daily[Activity Type for Pivots] = "Cycle")

Calendar[Date]

is being pulled from a separate date table generated by Excel in powerpivot, which is linked to the date of exercise. I'm trying to create a rolling summation of cycling for the year in question, which I'd previously had in a table using an expression like

SUM NEW = SUM OLD + This Week

The syntax appears correct to me, but I'm seeing no data returned, it's all blanks. Forgive me if this is obvious but I've only spent the last 5 days trying to get to grips with using measures in DAX/PPVT

Answer 1

Hi @Emily Hua-MSFT , thanks for the response,

I modified the expression having looked a bit further. I created a dates table from 1/Jan/2020 through to end 2024, and changed the expression to
=TOTALYTD([Distance Cycling],DATESYTD(calendar[Date])). I've made the table that long as I want the YTD to automatically update each year with a slicer.

If I select Pivot by months, this is now giving me values, but each month value is equal to the sum of that month only, whereas what I'm trying to do is Month 2 YTD = Month 2 + Month 1 etc.

There is no sensitive data here, I'm just trying to get used to using the expressions, playing around with my exercise data. Effectively I'm learning to do what I've been doing through SUM, SUMIF and SUMIFS, COUNT functions etc.

How do I share the file? Excel is not an allowed attachment type.
Many thanks
Dave

Answer 2

Emily, thanks,

what I have "discovered" so far.

1. I've rechecked my calendar table (generated through Power Pivot) and updated it to make sure it's OK, and that it is actually marked as a date table
2. The behaviour of the output seems dependent on the calendar table

With Calendar table end date = date of last entry in the activity table, the output pivot includes the latest data i.e. Jan 2021, BUT Jan 2020 is now zero's

If I reset the date to 31/12/2021 (I use UK Date system), then I get the following, with 2020 all showing zeros, but with 2021 showing what I am trying to do i.e. YTD right now, plus prediction to end of year as if no other activity took place, AND see the progression for all previous years I've included in the Pivot

The behaviour is the same whether I select month or week as the timestep

This is a snapshot of the data table I'm importing, there are more columns to the right, but I'm not importing those