Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

  • 1.  SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

    Posted Aug 21, 2020 04:42 AM

    I've searched high and low for the last hours trying to resolve this - trying many, many examples, but I cannot get 

    SAMEPERIODLASTYEAR or using this solultion from SQLBI to work.  

    https://www.sqlbi.com/articles/comparing-with-previous-selected-time-period-in-dax/

     

    My code is (trying both and loads of others:

    Custody EndLastYear = CALCULATE( SUM('Adult EOFY Custody'[Total Number]), FILTER(ALL('Date'), 'Date'[FYear] = MAX('Date'[FYear])-1), SAMEPERIODLASTYEAR('Date'[Date]) ) /* CALCULATE ( SUM('Adult EOFY Custody'[Total Number]), SAMEPERIODLASTYEAR ('Date'[Date]), ALL ('Date' ) )*/

    I get the same current years data returned and I can't fix this:

     

    Month Total Number Legal_Status Custody EndLastYear Custody ELY
    30/06/2013 0:00 2769 Remand 2769 2769
    30/06/2014 0:00 2795 Remand 2795 2795
    30/06/2015 0:00 3651 Remand 3651 3651
    30/06/2016 0:00 4170 Remand 4170 4170
    30/06/2017 0:00 4309 Remand 4309 4309
    30/06/2018 0:00 4502 Remand 4502 4502
    30/06/2019 0:00 4566 Remand 4566 4566
    30/06/2020 0:00 4188 Remand 4188 4188

     

     

    The zip file is here

    https://www.dropbox.com/s/9dfs7i19kw9q1zn/Custody_infographics%20model.zip?dl=0

     

    Please could someone help!

    Thanks so much

    Tracey



    ------------------------------
    Tracey Hemley
    ------------------------------


  • 2.  RE: SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

    Top Contributor
    Posted Aug 21, 2020 06:05 AM
    Hi Tracey,

    There is no relationship between your Date table and the Adult EOFY Custody table. Therefore, the time intelligence functions can't work.

    You need to create another Date table linked to the Adult EOFY Custody (or use an inactive relationship with the current date table and use a USERELATIONSHIP function in the measure).

    ------------------------------
    Vincent L.
    Chartered accountant - Expert-comptable
    ------------------------------



  • 3.  RE: SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

    Posted Aug 21, 2020 04:36 PM

    Hi Vincent,

    Yes I looked into the relationships and because "Adult EOF Custody" is a created table from "Adult Custody", I thought it might have used the Date relationship from there, as I was getting circular referencing when I tried to add the relationship.  So now I know, you need to add a new relationship table.

    I've done that not, you'll find the Date_M table.  No, no, still not working!!  Still doing the same thing.  Do you think it might be because I'm using a Financial year, up until June 20xx?  And the function SAMEPERIODLASTYEAR is trying to use the whole year to December?

    I've tried adding filters.  Still nothing.



    ------------------------------
    Tracey Hemley
    ------------------------------



  • 4.  RE: SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

    Top Contributor
    Posted Aug 22, 2020 05:57 AM
      |   view attached
    Hi Tracey,

    I just found the answer. You need to use the DateM['Date'] column in your visual instead of the Adult EOF... table Month, so the time intelligence will work.
    Also, there is no need for the USERELATIONSHIP function as your relationship is active.

    My measure is simply :
    PreviousYear = CALCULATE(
    SUM('Adult EOFY Custody'[Total Number]),
    SAMEPERIODLASTYEAR('Date_M'[Date])
    )

    Enclosed the PBIX corrected.

    ------------------------------
    Vincent L.
    Chartered accountant - Expert-comptable
    ------------------------------

    Attachment(s)



  • 5.  RE: SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

    Bronze Contributor
    Posted Aug 22, 2020 08:53 AM
      |   view attached

    Hi Tracey,

    Vincent is right; no need to use "userelationship.".

    Also, it seems you've over complicated the solution.
    No need for summary columns. 

    I wrote these 2 formulas

    Adult EOFY Custody new = CALCULATE( SUM ('Adults in Custody'[Number]), FILTER('Date', 'Date'[Month Number] = 6), FILTER('Adults in Custody', 'Adults in Custody'[Legal_Status] <> "Unk") ) DATEADD new = CALCULATE ( 'Adults in Custody'[Adult EOFY Custody new], DATEADD ( 'Date'[Date], -1, YEAR ) )

    and it's working fine.

    Enclosed the pbix.



    ------------------------------
    Marc Schroyen
    Liège Belgium
    ------------------------------

    Attachment(s)



  • 6.  RE: SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

    Posted Aug 22, 2020 05:41 PM
    Vincent,

    My life saver.  I could sleep well.  Thank you so much.

    I would never have worked out it was due to the Date value needing to come from the Date table, instead of the FACT table.  Thank you so much!

    ------------------------------
    Tracey Hemley
    ------------------------------



  • 7.  RE: SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

    Posted Aug 23, 2020 06:59 PM
    Hi Marc,

    Absolutely!  What a simpler version.  This is my first try at trying to work with Power BI. I found this data here is Australia on the NSW prison data - which I found interesting.

    I was trying to work out how to narrow this data - wondering if I needed to create a table, because the data was from all months, but the NSW site only shows stats from each financial year (they should do averages) - so I'm trying to replicate a pdf they have online.

    This has been such a great learning curve for me to see how PowerBI works.  And I thank you for showing me how to simplify our structures, because I was stuck on rather wanting to use a measure - but not knowing how!

    Thank you so much!!  I've learnt so much through you and Vincent in helping me solve this.  You've both given me strength to continue trying on.  I was feeling so despondent last week.

    Warm regards,
    Tracey


    ------------------------------
    Tracey Hemley
    ------------------------------



  • 8.  RE: SAMEPERIODLASTYEAR - Not returning last years data, but current. Help!

    Posted Sep 14, 2021 03:01 AM
      |   view attached

    Hi Tracey Hemley, looong time and gentleman.

    Relatively new to PBI and doing the hard yards at the moment.

    I have nearly the same problem and have spent a day and it is driving me insane.

    I have attached the original pbix with some popcorn data ( not true). One row only for the last day of the month and I want to show the data for the same month previous year.  SAMEPERIODLASTYEAR just not doing it for me.

    Thanks in Advance
    David



    ------------------------------
    David Chauncy
    Dev
    ------------------------------

    Attachment(s)