Prior YTD formula not working

[brittanyruffin]

When I do a standard YTD it is working as it should. However, when I try to do a Prior Year YTD it is giving me the value of the entire year vs the same YTD period.

YTD is showing data from 1/1/24 to 1/8/24. I need the Prior Year YTD to show 1/1/23 to 1/8/23 for example.

I have tried about 1000 different formulas but here is my most recent that I got by using Copilot within Power BI

//PY YTD = CALCULATE(

    SUM('vCorporateGandA'[Amount]),

    SAMEPERIODLASTYEAR(

        'CalendarTable'[Date].[Date]

    )

)

I'm getting $31.64M with this formula. When I filter by last year, date, etc manually get $2.37M.

I'm needing to put this in a table to compare multiple different values so I can't filter manually.

[bchager6 · Accepted solution]

@brittanyruffin  Check out @wdx223_Daniel 's solution here. It works.

PY YTD = 
VAR _END = MAX(CalendarTable[Date])
VAR _START = DATE(YEAR(_END), MONTH(_END), 1)
RETURN CALCULATE(SUM('vCorporateGandA'[Amount], SAMEPERIODLASTYEAR(DATESMTD(CalendarTable[Date])),CalendarTable[Date]<=EDATE(_END,-12))

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/SAMEPERIODLASTYEAR-return-a-wrong-re...

[danextian · Accepted solution]

Hi @brittanyruffin ,

SAMEPERIODLASTYEAR calculates the value in the same period last year relative to the current row. It does not calcualted YTD.  Here are sample formulas  =

sum ytd = 
CALCULATE ( [Sum of Values], DATESYTD ( Dates[Date] ) )

sum ytd py = 
CALCULATE ( [sum ytd], SAMEPERIODLASTYEAR ( Dates[Date] ) )

[v-xuxinyi-msft · Accepted solution]

Hi @brittanyruffin 

My sample:

You can create a measure as follows.

Measure 2 = 
VAR _startdate = DATEVALUE(DATE(2024, 1, 1))
RETURN
IF( 
    YEAR(SELECTEDVALUE('vCorporateGandA'[Date])) = YEAR(TODAY()) - 1 
    && MONTH(SELECTEDVALUE('vCorporateGandA'[Date])) <= MONTH(_startdate) 
    && MONTH(SELECTEDVALUE('vCorporateGandA'[Date])) <= MONTH(TODAY()) 
    && DAY(SELECTEDVALUE('vCorporateGandA'[Date])) >= DAY(_startdate) && DAY(SELECTEDVALUE('vCorporateGandA'[Date])) <= DAY(TODAY()), 
    1, 
    0
)

Prior Year YTD = CALCULATE(SUM('vCorporateGandA'[value]), FILTER( ALLEXCEPT('vCorporateGandA', 'vCorporateGandA'[id]), [Measure 2] = 1))

Is this the result you expect?

If I've misunderstood you, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community. Please remove any sensitive data in advance.

Best Regards,

Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian · Accepted solution]

Hi @brittanyruffin ,

SAMEPERIODLASTYEAR calculates the value in the same period last year relative to the current row. It does not calcualted YTD.  Here are sample formulas  =

sum ytd = 
CALCULATE ( [Sum of Values], DATESYTD ( Dates[Date] ) )

sum ytd py = 
CALCULATE ( [sum ytd], SAMEPERIODLASTYEAR ( Dates[Date] ) )

[brittanyruffin]

thank you! this worked exactly how i needed.

do you also know how i can get a Prior Year calculation for the current quarter?

i have this formula that gives me the current quarter amount without me having to change my filters each quarter on the visual.

Current Quarter Amount =

CALCULATE(

    SUM('vCorporateGandA'[Amount]),

    YEAR('CalendarTable'[Date]) = YEAR(TODAY()) &&

    QUARTER('CalendarTable'[Date]) = QUARTER(TODAY())

)

i'm needing this formula to show the prior year. i tried doing something similar to what you had above with but it did not work:

CALCULATE([Current Quarter Amount], SAMEPERIODLASTYEAR(Dates[Date]))

Any ideas?

[brittanyruffin]

thank you! this worked exactly how i needed.

do you also know how i can get a Prior Year calculation for the current quarter?

i have this formula that gives me the current quarter amount without me having to change my filters each quarter on the visual.

Current Quarter Amount =

CALCULATE(

    SUM('vCorporateGandA'[Amount]),

    YEAR('CalendarTable'[Date]) = YEAR(TODAY()) &&

    QUARTER('CalendarTable'[Date]) = QUARTER(TODAY())

)

i'm needing this formula to show the prior year. i tried doing something similar to what you had above with but it did not work:

CALCULATE([Current Quarter Amount], SAMEPERIODLASTYEAR(Dates[Date]))

Any ideas?

[bchager6 · Accepted solution]

@brittanyruffin  Check out @wdx223_Daniel 's solution here. It works.

PY YTD = 
VAR _END = MAX(CalendarTable[Date])
VAR _START = DATE(YEAR(_END), MONTH(_END), 1)
RETURN CALCULATE(SUM('vCorporateGandA'[Amount], SAMEPERIODLASTYEAR(DATESMTD(CalendarTable[Date])),CalendarTable[Date]<=EDATE(_END,-12))

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/SAMEPERIODLASTYEAR-return-a-wrong-re...