How Can I display the Current Value and Previous Value based on a Previous Day

1-The used data as below:

Date Current Val
2/18/20 60,027,505.89
2/19/20 62,517,208.90
2/20/20 62,192,277.30

2- The required output should be like that:

Date Current Val Previous Val
2/18/20 60,027,505.89 0
2/19/20 62,517,208.90 60,027,505.89
2/20/20 62,192,277.30 62,517,208.90


and So on..

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • This can be done as below:

    1-Add the Base Transaction data to the Data files

    2-Add a Date Dimension table and make sure it contains a previous date column for example:

    Date Previous Date
    2/17/20 2/16/20
    2/18/20 2/17/20
    2/19/20 2/18/20
    2/20/20 2/19/20
    2/21/20 2/20/20


    3-Create a Schema with 3 tables as below:

    TransactionsTable==>the source will be the Transaction data

    Date_Dim==>the source will be the Date dimension data with "Previous Date" Column

    Transactions_Alias==>An alias from TransactionsTable


    4- Create The joins as Below:

            1-Between Date_Dim.Date = TransactionsTable.Date

            2-Between Date_Dim.Previuos = Transactions_Alias.Date 

    5-Explore the Data and get a new Dashboard

            1-Make sure it is an aggregated insight

            2-Drag Date column into dimension area from Date_Dim Table

            3-Drag CurrentVal  into measures area from  TransactionsTable

            4-Drag CurrentVal As Previous Val  into measures area from  Transactions_Alias    


            5- The Output dashboard will be like that:

    **attached the used sample data files

    Like 1
  • Yes, that was what I was missing! Thank you!

    • Donald Clarke  you are always welcome :) 

Like1 Follow
  • Status Answered
  • 2 wk agoLast active
  • 3Replies
  • 20Views
  • 2 Following