Forum Discussion

Krisso1975's avatar
Krisso1975
Regular Visitor
4 years ago

PY Period figure

Hi All

 

Is there any way to copy this measure so that I can see last years same period figures?

 

This Period Billing = CALCULATE(Sum(Billing[SplitAmount]),Billing[uvw_PeriodDetails.Current]="Current")
 
I thought it would be the same period last year formula but as the above is a measure I can not find it to select it
 
Thanks

 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Krisso1975 , if you have a date then join it with date table

     

    CALCULATE(Sum(Billing[SplitAmount]),SAMEPERIODLASTYEAR('Date'[Date]))

     

    else have column like and then use in filter

     

    Year Type = Switch( True(),
    year([Date])= year(Today()),"This Year" ,
    year([Date])= year(Today())-1,"Last Year" ,
    Format([Date],"YYYY")
    )

     

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • Krisso1975's avatar
      Krisso1975
      Regular Visitor
      4 years ago

      Hi Amitchandak

      Preview
       
      Thanks for the reply. Is that code to go in the report or should I be entering this in power query as a column. I tried the first and the result was when I was expecting last year/this year.

      The data is joined using a date table and I successfully have same period for last year working for other columns in the report. I think my issue is that I want to base a measure from a measure in the report and I cant select it in the normal way

       

      Thanks

       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Krisso1975 ,

     

    What is this [uvw Period Details.Current] column? Is it a column to identify if the records is current or last year?

    Regardless this column, If you have date column, you could use year([date]) to get the year of date and year([date])-1 to get last year.

    So the measure shoule be like:

    last year =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        YEAR ( 'Table'[date] )
            = YEAR ( SELECTEDVALUE ( 'calendar'[date] ) ) - 1
    )
)

    If I misunderstood your meaning, please show some sample data and expected result.

     

    Best Regards,

    Jay

    • Krisso1975's avatar
      Krisso1975
      Regular Visitor
      4 years ago

      Hi Jay,

       

      In power query I have a table of period details that is merged in with the data i am using Current is a reference to if todays date falls between the 1st of the month and last of the month = Table.AddColumn(#"Changed Type1", "Current", each if([Current Date] >= [PeriodStartDate]) and([Current Date]<=[PeriodEndDate]) then "Current" else "Previous") which then I use to pull the current periods data.

       

      I tried your solution but got a blank result. I this something I could similar in power query to find the same period 1 year back rather than doing through Power BI measures?