Forum Discussion

Helper I

2 years ago

Solved

Prior YTD formula not working

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.

Need Help

Anonymous
2 years ago
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-result-when-the-date-is-the/m-p/1492006#M28694
danextian
2 years ago
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] ) )

Anonymous

2 years ago

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.

Prior YTD formula not working.pbix29 KB

5 Replies

Anonymous
Not applicable
2 years ago
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-result-when-the-date-is-the/m-p/1492006#M28694
danextian
Super User
2 years ago
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
  Helper I
  2 years ago
  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
  Helper I
  2 years ago
  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?
Anonymous
Not applicable
2 years ago
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.

Prior YTD formula not working.pbix29 KB