Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

DATEADD returns blank value

Estimated.

I have the following problem with some DAX formulas, where I want to calculate last year's sales using DATEADD.

The formulas would be as follows:

1) LYTD = CALCULATE([YTD],DATEADD('PM CHILE Premium'[Period],-1,YEAR)) (This is the one that has to show me the desired result)
2) YTD = TOTALYTD('Measures'[Measures],'PM CHILE Premium'[Period])
3) Measures = SWITCH(TRUE(),
VALUES(Measurement[Measure]) = "Counting Units", INT(SUM('PM CHILE Premium'[Counting Units]))/1,
VALUES(Measurement[Measure]) = "Standard Units", INT(SUM('PM CHILE Premium'[Standard Units]))/1,
VALUES(Measurement[Measure]) = "Units", INT(SUM('PM CHILE Premium'[Units])/1),
VALUES(Measurement[Measure]) = "Value (List LC)", INT(SUM('PM CHILE Premium'[Value (List LC)])/1),
VALUES(Measurement[Measure]) = "Value (List USD)", INT(SUM('PM CHILE Premium'[Value (List USD)])/1)
)
Formula 1, I should return the accumulated sale thrown by "Measures" until July 2022 (which is the information I have available in the Base). However, when I run it, it returns a blank value.
Screenshot of the base to see that the format of the dates is OK.
Panel with blank result:
ADDITIONAL INFORMATION: The base only delivers the dates as 01-MONTH-YEAR, in short, only delivers the month and year, indicating the day with 1.
Greetings and in advance, thank you!

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Dont Use YTD in Lytd

    LYTD= CALCULATE([sum of sale],DATEADD('PM CHILE Premium'[Period],-1,YEAR))


    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      Thank you dear, unfortunately it did not work for me, but by following your advice, I was inspired to make the following modifications:

      I created a Calendar Table and related my Base to it.

      1) LYTD = CALCULATE([Medidas],DATEADD(Calendario[Date],-1,YEAR))

      2)

      Measures = SWITCH(TRUE(),
      VALUES(Measurement[Measure]) = "Counting Units", INT(SUM('PM CHILE Premium'[Counting Units]))/1,
      VALUES(Measurement[Measure]) = "Standard Units", INT(SUM('PM CHILE Premium'[Standard Units]))/1,
      VALUES(Measurement[Measure]) = "Units", INT(SUM('PM CHILE Premium'[Units])/1),
      VALUES(Measurement[Measure]) = "Value (List LC)", INT(SUM('PM CHILE Premium'[Value (List LC)])/1),
      VALUES(Measurement[Measure]) = "Value (List USD)", INT(SUM('PM CHILE Premium'[Value (List USD)])/1)
      )
      However, this happens:
      The YTD gives me correct, because it is the sum of January to July 2022, but the LYTD now gives me the total of the year 2021, when it should be January to July 2021 (which should be 878.8 and not 1560 as it appears in the image).
      Best regards!
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    You must have a calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship from the Period column to the Date column of the Calendar Table.  To your visual, filter and slicers, drag any date field from the Calendar Table.  Write this measure

    LYTD = CALCULATE([YTD],SAMEPERIODLASTYEAR(Calendar[Date]))

    Hope this helps.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      Thank you dear!

      I followed the advice to create a month column in the calendar table, and in the visual filter of the card, select the months that interested me to calculate growth (1, 2, 3,... 7).

      And then, I used the following formulas.

      Growth % = [YTD]/[LYTD]-1
      YTD = TOTALYTD('Measures'[Measures],Calendar[Date])
      LYTD = CALCULATE([Measures],DATEADD(Calendar[Date],-1,YEAR))
      Best regards
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, Syndicate_Admin ;

    Try it.

    Ytd = CALCULATE(SUM('Table'[value]),DATESYTD('Table'[Date]))
    Lytd = 
 var _max=CALCULATE(MAX('Table'[Date]),ALL('Table'))
 return CALCULATE(SUM('Table'[value]),FILTER('Table',[Date]>=DATE(YEAR( _max)-1,1,1)&&[Date]<=DATE(YEAR(_max)-1,MONTH(_max),DAY(_max))))

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, Syndicate_Admin ;

    Is your problem solved?  If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.