Display dates of matching sales of last year

[RamyaRapolu]

Hi ,

I have a table with dates and sales, I have calculated cummulative totals(measure) by year. I want to get the date when the same sales was achived last year. In the example, the Cummulative sum 880 or above was achived on 1/4/2020. so display 1/4/2020.

[RamyaRapolu]

Hi @V-pazhen-msft 

Thanks for the solution. But I need to compare the current year date's sales data with previous year sales date and print the date, where the sales have been reached/crossed in previous year date. Below is the expected output.

[V-pazhen-msft]

@RamyaRapolu 

Compare value within the same field requires complicate logic in terms of row context. I create the sample pbix demonstration both Column solution and Measure solution, You can check the pbix for detail.

Basically I you need to compare the last dates' sales(800 in this example) with the above dates to find the min date that reaches the last dates cumulative sales(882).

You need a measure of the LastDate cumutotal:

Maxdate sale = CALCULATE(MAX([Column CumuSale]),FILTER(ALL('Table'),[Date]=MAXX(ALL('Table'),[Date])))

There are the column and measure solutions:

Column CumuSale = CALCULATE(SUM('Table'[Sales]),FILTER('Table',[Date]<=EARLIER([Date]) &&[Date].[Year]=EARLIER([Date].[Year])))
Column return = IF(([Date])=MAX([Date]),CALCULATE(MIN([Date]),FILTER('Table',[Column CumuSale]>[Maxdate sale])))

Measure Cumusales = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),YEAR([Date])=YEAR(MAX([Date])) && [Date]<=MAX([Date])))
Measure return = IF(MAX([Date])=MAXX(ALL('Table'),[Date]),CALCULATE(MIN([Date]),FILTER(ALL('Table'),[Measure Cumusales]>=[Maxdate sale])))

Best regards
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[RamyaRapolu]

Thanks for the reply @amitchandak 

Assuming that we have date table, what could be the DAX expression to get my expected output.

[amitchandak]

@RamyaRapolu , based on what I got time intelligence and date table should help

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

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.