Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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 'l...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 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
    4 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!