Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Finding a value where another column in the same table has the same column value but -1

Hola kind people!

 

A bit of an exhaustive title, but this captures what I am trying to do from an analytical perspective, I believe.

 

Functionally put, I want to find a value (e.g. sales) of 'last year'.

So I have a column 'Value', a column 'Year-Month-Day' (this is a date column), and separate columns for 'Year', 'Month', 'Day', and even 'Week' (these are numeric, so you could e.g. look for 'current value of Year but then -1).

 

I want to avoid creating a specific date table for this, as I find this to be silly - it should be unnecessary - and not a neat solution at all. Think about e.g. four-week periods versus months, think about different starting and ending dates of years (not every year begins at 1 January and end at 31 December), think about using 'week end' values instead of simple days, think about inconsistent date ranges that maybe alternate four and three week periods, et cetera. I will define dates in my tables, and ensure that I can make a relation between them directly in my processing before I load the tables into PowerBI.

 

I have tried multiple options.

The ones I wrote down that produced a blank result were:

Value_YA = CALCULATE(SUM(Table[Value]), PREVIOUSYEAR(Table[Year-Month-Day]))
Value_YA = CALCULATE(SUM(Table[Value]), SAMEPERIODLASTYEAR(Table[Year-Month-Day]))

Value_YA = CALCULATE(SUM(Table[Value]), SAMEPERIODLASTYEAR(Table[Year-Month-Day]), ALL(Table))

 

The ones that I wrote down that produced errors were:

Value_YA = LOOKUPVALUE(Table[Value], Table[Year],Table[Year]-1)
Value_YA = FILTER(Table[Value], RELATED(Table[Year]-1)

 

Google keeps pointing me to PREVIOUSYEAR and SAMEPERIODLASTYEAR functions, which do not work with my current knowledge, or telling me to create a separate date table, which is not an acceptable solution.

 

How do I find a value where another column's value is -1?

Thank you very much in advance! 🙂

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , All options you use work best date, date from a date table joined with Date of your fact/table.

     

    Also, these work best as measures. Calculated column, you need take care of few things. 

     

    Then the previous year is last full year. Sameperiodlastyear is different 

     

    Value_YA = CALCULATE(SUM(Table[Value]), PREVIOUSYEAR(Date[Date]))
    Value_YA = CALCULATE(SUM(Table[Value]), SAMEPERIODLASTYEAR(Date[Date]))

     

    examples 

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))

     

    refer

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

    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

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for your reply!

       

      I do not want PowerBI to force me into using a separate date table when something as simple as this should be a normal core functionality.

       

      I (think I) know what PREVIOUSYEAR and SAMEPERIODLASTYEAR do, both could be useful for various usecases, so I experimented with both. Neither work in my current set-up, presumably because I want to do all this in one table, and not using a separate date table.

       

      Can you reply to me how I would do this using only my facts table, and not a separate date table?

       

      Thank you in advance!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    A small update:

     

    Value_YA = CALCULATE(SUM(Table[Value]), FILTER(ALLSELECTED(Table),Table[Year-Month-Day]=MAX(Table[Year-Month-Day])-364))
     
    This works! Except... Except that when you filter the table on e.g. year 2022, the calculation finds it impossible to retrieve the 2021 numbers it needs to give me the numbers from a year ago. Can that be circumvented?