Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Value a year ago

Hi,

 

I have two slicers (Year and month) and BCiCalendar where I can choose a specific day in a given month. I displayed:

1) Profit on a given day (for example: 10.02.2018);

2) Running total from the beginning of the given month (for example: from 01.02.2018 to 10.02.2018).

 

Now I would like to add the same indicators but for last year ie.

1) Profit on the same day last year (for example 10.02.2017);

2) Running total from the beginning of the given month last year (for example from 01.02.2017 to 10.02.2018).

 

I tried to use sameperiodlastyear, dateadd, everything:) but it shows me nothing, or at best the sum for the entire month, not the day or running total..

Need Help
  • FarhanAhmed's avatar
    FarhanAhmed
    6 years ago

    Create a new Table name it "Date" using "CalendarAuto()"

     

     

     

    Now Join this date with your  Table (OrderDate and Date)

     

     

    Mark this new date table as "Date Table"

     

     

     

    Create your calculations based on this date table.... Change your calcultion dates to use these date column from date table.

17 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

     

    Profits Amount LY = CALCULATE(SUM(Sales[Profit Amount]), SAMEPERIODLASTYEAR('Date'[Date]))
     
    Profit RT =
    VAR MaxDate = MAX ( 'Date'[Date] )
    RETURN
    CALCULATE( SUM(Sales[Profit Amount]), 'Date'[Date] <= MaxDate )
     
     
    Sales YTD = CALCULATE( [Sales Amount], DATESYTD( 'Date'[Date] ) )
     
     


    Regards,

    Harsh Nathani


    Appreciate with a Kudos!! (Click the Thumbs Up Button)

    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous !

       

      For Profits Amount LY displays "blank" whatever I select..

      Profit RT works but only for current year:(

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

         

         

         

         

        Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

         

        Sales Amount LY = CALCULATE(Sales[Sales Amount], SAMEPERIODLASTYEAR('Date'[Date]))

         

        Sales YTD = CALCULATE( [Sales Amount], DATESYTD( 'Date'[Date] ) )

         

        i have done it from a Sales perspective . You can do it from a Profit Perspective.

         

         

        https://www.sqlbi.com/articles/computing-running-totals-in-dax/
        Regards,

        Harsh Nathani


        Appreciate with a Kudos!! (Click the Thumbs Up Button)

        Did I answer your question? Mark my post as a solution!

         

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

    Anonymous ,

    you can year behind measure

    Last year Day  = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

    Last year Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-365))

     

    refer: https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

     

    For second one

    Cumm Sales =
    var _max =maxx(allselected(Date),Date[Date])
    var _min = date(year(_max)-1,month(_max),1)
    return

    CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date]) && date[date] >=_min

    ))

     

    or

    Cumm Sales =
    var _max =maxx(allselected(Date),Date[Date])
    var _min = date(year(_max)-1,month(_max),1)
    return

    CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(date[date]) && date[date] >=_min

    ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak FarhanAhmed Anonymous Still 'blank'. Definetely the problem arises when I mark a specific day in the calendar. It works like a dream for the whole month.