Forum Discussion

sayali_deshmukh's avatar
sayali_deshmukh
Helper III
6 years ago
Solved

Calculated Column for previous date values

Hi, Have a table as per below -  Need help for calculating yesterday's actual and day before yesterday's actual using DAX.
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    sayali_deshmukh 

    You can create measure like with date calendar

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

    2 Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Day))

  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi sayali_deshmukh,

     

    Would you please refer to the following calculated column:

    Yesterday's Update = CALCULATE(MAX('Table'[Today's Update]),ALL('Table'),'Table'[Task] = EARLIER('Table'[Task]),'Table'[Date] = EARLIER('Table'[Date])-1)



Day Before Yesterday's Update = CALCULATE(MAX('Table'[Today's Update]),ALL('Table'),'Table'[Task] = EARLIER('Table'[Task]),'Table'[Date] = EARLIER('Table'[Date])-2)

    Best Regards,

    Dedmon Dai

amitchandak's avatar
amitchandak
Super User
6 years ago

sayali_deshmukh 

You can create measure like with date calendar

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

2 Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Day))

sayali_deshmukh's avatar
sayali_deshmukh
Helper III
6 years ago

Hi amitchandak 

Thanks for your help.

Could you suggest a way for text inputs as well ?

 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    For that prefer to have column like this

    maxx(filter(table,table[task]=earlier(table[task]) && table[Date]=earlier(table[Date])-1),[Todays Plan])

     

    Or try the same formula(provided in last update) with Max function and text column

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    6 years ago

    Hi sayali_deshmukh,

     

    Would you please refer to the following calculated column:

    Yesterday's Update = CALCULATE(MAX('Table'[Today's Update]),ALL('Table'),'Table'[Task] = EARLIER('Table'[Task]),'Table'[Date] = EARLIER('Table'[Date])-1)



Day Before Yesterday's Update = CALCULATE(MAX('Table'[Today's Update]),ALL('Table'),'Table'[Task] = EARLIER('Table'[Task]),'Table'[Date] = EARLIER('Table'[Date])-2)

    Best Regards,

    Dedmon Dai

    • sayali_deshmukh's avatar
      sayali_deshmukh
      Helper III
      6 years ago

      Hi v-deddai1-msft 

      Thanks for your help.

      I was able to get the columns as per your inputs.

    • sayali_deshmukh's avatar
      sayali_deshmukh
      Helper III
      6 years ago

      Hi v-deddai1-msft 

      Is there any way to extend this solution to calculate previous dates update continuously so that all updates can be gotten as -

      Today's update, yesterday's update, day before yesterday's update and so on...

      - without creating the columns