Previous Year to Date until certain Date.

[jmolina]

Hello everyone, along with saying hello, I hope you can help me with the following:

I have a dataset that has several million pieces of data, from which I need to calculate the YTD and Previous YTD. The problem I have is that the YTD is not up to the current date, but it is up to the maximum date that the "RETAIL" has, for example

retail     year       date              current date
retail1   2021    01-16-2022    01-17-2022
retail1   2022    01-15-2022    01-17-2022 (maximum date or current date)

I currently have this formula for the PYTD

PYTD=
CALCULATE ([YTD],
FILTER (
SAMEPERIODLASTYEAR ( CALENDAR[Date] ),
CALENDAR[Date]
< DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
)
)

This dax gives me the PYTD as a result until 01-16-2021, it should be equal until 01-15-2021 according to the YTD max date.

Please Help.

Christian.

[Ashish_Mathur]

Hi,

Your question is not clear.  Share some data and show the expected result clearly (with an explanation).

[jmolina]

Hi, thanks for response:

I have 4 "BRANDS" within the data, which have sales for both 2021 and 2022, I need the PYTD to be calculated until the last date indicated by the YTD, for example:

The PYTD of "BRAND4" should be until the same day that the YTD indicates, that is, until 01-22-2022, my current formula calculates me until 01-23-2022 (considers the maximum of the date of 2021, and it should consider the maximum date of 2022 as limit per different BRAND)

Thanks in advance

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[jmolina]

Ashish, hi, I have tried your formulas and the same thing still happens to me, when selecting a retailer that has its total sales from 2022 until 06-04-2022, the formula for SPLY total sales continues to consider until 06-05-2022 when comparing, it should show only until 06-04-2022.

HOW IS:

HOW IT SHOULD BE:

Please help

[Ashish_Mathur]

Hi,

Try this

New measure = if(isblank([Total sales]),blank(),[Total sales in SPLY])

Hope this works.

[jmolina]

Ashish, this work perfect!, but im still having a little issue, how do i do the same for month to date and  previuos month to date??? i try to simulate the New Mesaure for MTD and PMTD without suceed, It must meet the same conditions as the formula you gave me.

Thanks in advance!

[Ashish_Mathur]

What measures have you written?

[jmolina]

Ashish, i have this measures:

#MTD = CALCULATE([TOTAL SALES],
DATESBETWEEN(Calendar[Date],
FIRSTDATE(DATEADD(Calendar[Date],0,MONTH)),
IF(MONTH(MIN(Calendar[Date])) = MONTH(MAX(Calendar[Date])),EDATE(MAX(Calendar[Date]),0), LASTDATE(DATEADD(Calendar[Date],0,MONTH)))))

#MTD-1 = IF([TOTAL SALES] = BLANK(), BLANK(),
CALCULATE([Ventas Selecc],
DATESBETWEEN(Calendar[Date],
FIRSTDATE(DATEADD(Calendar[Date],-12,MONTH)),
IF(MONTH(MIN(Calendar[Date])) = MONTH(MAX(Calendar[Date])),EDATE(MAX(Calendar[Date]),-12), LASTDATE(DATEADD(Calendar[Date],-12,MONTH))))))

The MTD-1 It must meet the same conditions as the "New Measure" you gave me for the Previous YTD.

[jmolina]

Asish, any idea?

[Ashish_Mathur]

Hi,

I have off this thread for a while.  Shar some data, describe the question and show the expected result.

[jmolina]

I cannot find a solution,  i just re devolped everything, and calculate " PER CATEGORY" then just SUM by part, so in that way i can handle the date problem.

Regards