Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Previous Year YTD calculation

Hi,

I have a report which should show up  2021 Actual values, 2021 YTD Actual values and Previous Year YTD Actual values for report below.

 

I have calculations for 2021 Actual values and 2921 YTD Actual values but I am not able to figure out the Previous Year YTD Actual values. I used below DAX

PY YTD Actual = VAR x = -TOTALYTD(sum(Append1[AMT_ACTUAL]),SAMEPERIODLASTYEAR(Append1[Date].[Date]))/1000
Return
if (
HASONEFILTER(AcctCategory[Acct Type]),x,sumx(AcctCategory,-TOTALYTD(sum(Append1[AMT_ACTUAL]),SAMEPERIODLASTYEAR(Append1[Date].[Date]))/1000))
here Append1 is the table name
AcctCategory is another table which has Revenue and Expense as categories.
The total is wrong here it should be 70,692-71,006=-314 but its showing -331 which is wrong.
 
Please help me to solve this.
 
Thanks in Advance,
Neelofar Shama.
Need Help

6 Replies

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hello Anonymous ,

     

    Some of the following links are on the same line. Please refer them and let me know if that doesn't help you.
    https://www.kasperonbi.com/get-the-ytd-of-the-same-period-last-year/

    Or maybe the following DAX would help you:

    Previous Year YTD = 
VAR LastYearStartDate = DATEADD(DATEADD(DATEADD(ENDOFYEAR ( 'Date[Date] ),-1,YEAR),1,DAY),-1,YEAR)
VAR LastYearEndDate = DATEADD ( ENDOFMONTH ( 'Date'[Date] ), -1, YEAR )
RETURN
CALCULATE (
    SUM(Append1[AMT_ACTUAL]),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date]
            >= LastYearStartDate 
            && 'Date'[Date] <= LastYearEndDate 
    )
)

     

    Let me know if nothing of the above helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Rajul,

       

      Thank you for the reply, I tried your DAX calculation, with name "Measure", this is also giving me wrong values as you see below.

      Its yeilding 22436015 which is wrong.

      Could you please suggest some other alternative to this.

      Thanks in Advance,

      Neelofar Shama.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous ,

    You seem to have a problem with the total. You can use this measure:

    HASONEVALUE =
var _new=SUMMARIZE(' AcctCategory ', ' AcctCategory[Acct Type],"_value",[ PY YTD Actual])
return IF(HASONEVALUE(' AcctCategory[Acct Type]), [ PY YTD Actual],SUMX(_new,[_value]))

     

    Best Regards,

    Liu Yang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for the Reply but his is also giving e wrong values its yeilding -331 but it should yeild -314

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous  ,

     

    can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

     

    Best Regards,

    Liu Yang

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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    1 year ago
    YTD AA =
    VAR _FechaInicioAA = CALCULATE( MIN(CALENDAR[Date]), CALENDAR[Year] = MAX(CALENDARIO[Year]) - 1 )
    VAR _FechaMaxAA = MAX(CALENDAR[Date]) - 365
    RETURN
    CALCULATE(
    SUM(SELL_OUT[SELL_OUT]),
    DATESBETWEEN(CALENDAR[Date], _FechaInicioAA, _FechaMaxAA)
    )
    THAT'S THE GOOD ONE, JUST INTERPRET IT WITH YOUR DATABASE, IN ESSENCE THAT'S