DATEADD returns blank value

[Syndicate_Admin]

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!

[v-yalanwu-msft]

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.

[v-yalanwu-msft]

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.

[Ashish_Mathur]

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]

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

[PaulDBrown]

Please follow the recommendations in this thread to help us help you

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

[Anonymous]

Dont Use YTD in Lytd

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

[Syndicate_Admin]

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!