Forum Discussion

khappersett's avatar
khappersett
Resolver I
8 years ago
Solved

YTD Measure Summing by Row

I was using a date table created in and imported from Excel, but switched to a date table I created via a calculated table in Power BI. After switching, my YTD measures are giving me cumulative sums and I can't figure out why. Any help is appreciated!

 

 

2017 YTD Booked$ = Calculate(sum('Order Detail-Bookings'[BookedDollars]), DATESYTD('Date Table'[Date]))
2016 YTD Booked$ = CALCULATE(sum('Order Detail-Bookings'[BookedDollars]),dateadd(filter(datesytd('Date Table'[Date]),'Date Table'[Date]< TOday()),-1,year))
Need Help
  • khappersett's avatar
    khappersett
    8 years ago

    For some reason if I created a second date column in my date table....

     

     

    Date Ref = 'Date Table'[Date].[Date]

     And changed my YTD measures to reference this column instead....

     

     

    2017 YTD Booked$ = Calculate(sum('Order Detail-Bookings'[BookedDollars]), DATESYTD('Date Table'[Date Ref]))

    It fixed the problem. I have no idea why, but it works now.

16 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi khappersett,

     

    You should use the TimeIntelligence of PBI using the TOTALYTD formula:

     

    2017 YTD Booked$ =  TOTALYTD(sum('Order Detail-Bookings'[BookedDollars]),'Date Table'[Date])
    2016 YTD Booked$ = 
TOTALYTD (
    SUM ( 'Order Detail-Bookings'[BookedDollars] ),
    DATEADD ( 'Date Table'[Date], -1, YEAR )
)

    Regards,

    MFelix

      • MFelix's avatar
        MFelix
        Super User
        8 years ago

        Hi khappersett,

         

        How is your data set up, do you have a single line for each order detail by day or do you have any values that are sum.

         

        Regards,

        MFelix