YTD Measure Summing by Row

[khappersett]

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))

[MFelix]

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

[khappersett]

That is giving me the same problem.

[MFelix]

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

[khappersett]

My date table....

Date Table = 
ADDCOLUMNS(
CALENDAR(DATE(2010,01,01),DATE(2025,12,31)),
"Day", DAY([Date]),
"Day Name", FORMAT([Date], "ddd"),
"Month Name", FORMAT([Date], "mmm"),
"Month Number", MONTH([Date]),
"Quarter", "Q" & ROUNDUP(MONTH([Date])/3,0),
"Quarter Year", FORMAT([Date], "yy") & "Q" & ROUNDUP(MONTH([Date])/3,0),
"Year", YEAR([Date]))

[khappersett]

There is one line for each item per order. So if there is an order with three items, that order will have three rows in the table. Booked dollars is a sum in my query - is that what you mean? Sum(ITEM_REF.QTY * ITEM_REF.U_PRICE) AS BookedDollars

I think it is an issue with my date table somehow. The YTD measures were working fine until I switched my date table from a static, imported excel file to a PBI generated table.

[MFelix]

Hi @khappersett,

I believe that your problem is relating with the Sum(ITEM_REF.QTY * ITEM_REF.U_PRICE) if you are calculating line by line and them sum it up you need to use the SUMX so you iterate over every row and then on the end you have the total sum of those values.

Try to do the SUMX in your Bookeddollars and then see if it works.

Regards,

MFelix

[khappersett]

Booked dollars is not a DAX calculation done in Power BI - it is brought in from my database using SQL and my query brings in a table that is correct so don't think this is the issue. Unless you think I should be using SUMX in my YTD booked $ calculations? Tried that and it does not work either - gave me the same result as before.

test 2017 ytd booked $ = TOTALYTD(SUMX('Order Detail-Bookings', 'Order Detail-Bookings'[BookedDollars]), 'Date Table'[Date])

[MFelix]

On your print the first column values are correct? Are those the values for each Quarter or are those already duplicated.

[khappersett]

The first column "2017 Booked$" is correct, so "2017 YTD Booked$" (the third column) should match it. 

[MFelix]

f the first columns is correct and its a QTD value then your YTD is correct you need to use a QTD calcuation an not a YTD to achieve the same values as in column 1.

If you have 23.862.935 in FY you can see that value in Q3 and Q4 of the 2017 YTD booking nothing is duplicated,

Making YTD calculations matching your quarte values would give you a 71 M in Q1 and 0,6 in Q2, don't believe that would be the correct Quarter values.

Regards,

MFelix

[khappersett · Accepted solution]

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.

[MFelix]

Hi @khappersett,

Sorry for insisting on this but what is your total ytd value that you should have at current date? 23M

Because I'm getting confused about how do you have you data calculated previously and now, just want to help you in the best way possible.

Regards,

MFelix

[khappersett]

Total 2017 YTD should be $23.86M, so the total has always been correct. The issue was how it was giving me a cumulative total for each quarter, rather than breaking it up correctly per each quarter. So Q4 should have $0 since Q4 hasn't started.

[MFelix]

OK, so thje problem is that you are calculating a YTD measure and you need to use a QTD calculation use this formulas below and everything should be ok.

2017 QTD Booked$ =  TOTALQTD(sum('Order Detail-Bookings'[BookedDollars]),'Date Table'[Date])

2016 QTD Booked$ = 
TOTALQTD (
    SUM ( 'Order Detail-Bookings'[BookedDollars] ),
    DATEADD ( 'Date Table'[Date], -1, YEAR )
)

Regards,

MFelix 

[khappersett]

Well I already fixed my problem without having to make new measures. If that was the case, I'd have to use a different measure for every time period (quarters, weeks, days, etc). This YTD measure works fine now to just have one YTD meaure and I can break it up by quarters, months, weeks, etc. just fine. It was an issue with the date table reference.

[khappersett]

I have figured out what is causing the problem, but not sure how to fix it yet. Before I switched my date table, my YTD measure was as follows...

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

After switching my date table, the YTD measures were resulting in blanks unless I take out the .[Date] at the end so it became....

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

This is what I have currently. So it gives me a blank with the .[Date] at the end, but is summing my rows strangely without the .[Date] at the end.