Forum Discussion

DebbieE's avatar
DebbieE
Community Champion
2 years ago
Solved

SAMEPERIODLASTYEAR bringing back current values

This has happened after working on 

Solved: Re: KPIs visuals Running incredibly slowly - Microsoft Fabric Community

 

I have some DAX that is basically doing a distinct count on the person ID

Total People = CALCULATE (
    SUMX (
        VALUES ( 'Dim Person'[Person ID] ),
        1
    ),
    'Fact People'
)

And that gives me my total people

 

I have a date dimension connected by Date Key to Snapshot date key in the fact table. And have another DAX

 

No Pupils Same Period Last Year = CALCULATE (
    SUMX (
        VALUES ( 'Dim Person'[Person ID] ),
        1
    ),
    'Fact People',(SAMEPERIODLASTYEAR('Dim Date'[Date]
)))
 
But this is giving my the current 
 
Year          Period          Total People            No Pupils Same Period Last Year
2022         P2                10000                     10000
2023         P2                10394                      10394
 
Giving these examples of P2 (From the date table which is marked as date)
It should have  10000 for 2023 but its not doing it.
 
I am completely lost at this point again. Have I missed anything?

 

Need Help
  • Vallirajap's avatar
    Vallirajap
    2 years ago

    Hii DebbieE 

    Try this for the above senario,

     

    No Pupils Same Period Last Year = CALCULATE ( SUM(Total People),
       SAMEPERIODLASTYEAR('Dim Date'[Date].[Date]
    ))
     

    Did I answer your question?

    Mark my post as a solution, this will help others...!

    Hit the kudo also,

    Thank you

  • Ahmedx's avatar
    Ahmedx
    2 years ago

    ply try this

    m1 = 
VAR _LastYear = MAX('Dim Date'[Year]) -1
RETURN
CALCULATE (
    SUMX (
        VALUES ( 'Dim Person'[Person ID] ),
        1
    ),
    'Dim Date'[Year] = _LastYear
)

4 Replies

  • DebbieE's avatar
    DebbieE
    Community Champion
    2 years ago

     Yep I had done the DAX slightly wrong and resolved it by the above. Thank you

  • Vallirajap's avatar
    Vallirajap
    Resolver III
    2 years ago

    Hii DebbieE 

    Try this for the above senario,

     

    No Pupils Same Period Last Year = CALCULATE ( SUM(Total People),
       SAMEPERIODLASTYEAR('Dim Date'[Date].[Date]
    ))
     

    Did I answer your question?

    Mark my post as a solution, this will help others...!

    Hit the kudo also,

    Thank you

    • DebbieE's avatar
      DebbieE
      Community Champion
      2 years ago

      I already have this and while it works to get back to the same period last year. It doesnt get me back to the last snapshot

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    ply try this

    m1 = 
VAR _LastYear = MAX('Dim Date'[Year]) -1
RETURN
CALCULATE (
    SUMX (
        VALUES ( 'Dim Person'[Person ID] ),
        1
    ),
    'Dim Date'[Year] = _LastYear
)