A problem with a YTD equation

[Roy_B]

I have a data table and the first three columns show weekly information. A slicer is used to select a week within the year. The other tree columns shows the year to date (start of the year to the latest date that the user selected) of the first three columns. But the user can also filter by company, product, color, and country.

This is what I have currently the problem is that the totals don’t add up, it just shows the minimum value.

Test_CTYTD =

Var maxDT = LASTDATE(viwCustomerSalesComparison[SaturdayDate])

var Cust = MIN(viwCustomerSalesComparison[CustomerName])

var vcol = MIN(viwCustomerSalesComparison[VarietyColor])

var vNam = MIN(viwCustomerSalesComparison[VarietyName])

var meth = MIN(viwCustomerSalesComparison[Method])

Var tot = sumx(ALLSELECTED(viwCustomerSalesComparison[boxesTW], viwCustomerSalesComparison[Date],viwCustomerSalesComparison[CName],viwCustomerSalesComparison[VName],viwCustomerSalesComparison[Color],viwCustomerSalesComparison[Method]),

IF(AND(viwCustomerSalesComparison[Date] <= maxDT,

   AND(viwCustomerSalesComparison[CName] = Cust,

   AND(viwCustomerSalesComparison[Color] = vcol,

   AND(viwCustomerSalesComparison[VName] = vNam,

       viwCustomerSalesComparison[Method] = meth)))),

viwCustomerSalesComparison[boxesTW], 0))

return tot

[amitchandak]

@Roy_B , Not very clear. Do you have date. That you should prefer time intelligence with date table .

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Why TI fails - https://youtu.be/OBf0rjpp5Hw

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Roy_B]

this equation almost worked

Test_CTYTD = 
Var maxDT = LASTDATE(viwCustomerSalesComparison[SaturdayDate])
var Cust = MIN(viwCustomerSalesComparison[CustomerName])
var vcol = MIN(viwCustomerSalesComparison[VarietyColor])
var vNam = MIN(viwCustomerSalesComparison[VarietyName])
var meth = MIN(viwCustomerSalesComparison[Method])
var ttbl = ALLSELECTED(viwCustomerSalesComparison[boxesTW], viwCustomerSalesComparison[SaturdayDate],viwCustomerSalesComparison[CustomerName],viwCustomerSalesComparison[VarietyName],viwCustomerSalesComparison[VarietyColor],viwCustomerSalesComparison[Method],viwCustomerSalesComparison[SalesRegion],viwCustomerSalesComparison[OrderOrShipments],viwCustomerSalesComparison[SalesMan])

Var tot = sumx(ttbl, 
IF(AND(viwCustomerSalesComparison[SaturdayDate] <= maxDT, 
   AND(IF(ISFILTERED(viwCustomerSalesComparison[CustomerName]), viwCustomerSalesComparison[CustomerName] = Cust, TRUE()), 
   AND(IF(ISFILTERED(viwCustomerSalesComparison[VarietyColor]), viwCustomerSalesComparison[VarietyColor] = vcol, TRUE()), 
   AND(IF(ISFILTERED(viwCustomerSalesComparison[VarietyName]),  viwCustomerSalesComparison[VarietyName] = vNam, TRUE()),
       IF(ISFILTERED(viwCustomerSalesComparison[Method]),       viwCustomerSalesComparison[Method] = meth, TRUE()))))), 
viwCustomerSalesComparison[boxesTW], 0))

return tot

But when I looked at two diffrent customers the grand totals did not total up