Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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))
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] ) )
Proud to be a Super User!
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.
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.
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] ) )
Proud to be a Super User!
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.
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?
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.
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 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))