Forum Discussion

Sertan_CPH's avatar
Sertan_CPH
Frequent Visitor
4 years ago

Change tracking between dates

Look at the below example. I have a basket of fruits and I update the fruits and the price everyday. One day I have 4 fruits and the next day I am missing the orrange. Prices are also different.   ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Sertan_CPH Here's a way to generate the necessary missing rows and calculate the change in Price:

Table17a = 
    VAR __Table =
        ADDCOLUMNS(
            GENERATE(
                DISTINCT('Table17'[Date]),
                DISTINCT('Table17'[Fruit])
            ),
            "Price",LOOKUPVALUE(Table17[Price],Table17[Date],[Date],Table17[Fruit],[Fruit])+0
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "Change",
                VAR __PreviousDate = MAXX(FILTER(__Table,[Date]<EARLIER([Date]) && [Fruit]<=EARLIER([Fruit])),[Date])
                VAR __PreviousPrice = MAXX(FILTER(__Table,[Date]=__PreviousDate && [Fruit]=EARLIER([Fruit])),[Price])
            RETURN
                [Price] - __PreviousPrice
        )
RETURN
    __Table1
  • Sertan_CPH's avatar
    Sertan_CPH
    Frequent Visitor
    4 years ago

    Hi Greg_Deckler  I like your approach but I am getting an error "A table of multiple values was supplied where a single value was expected."

    What can be going wrong here?

     

      • Sertan_CPH's avatar
        Sertan_CPH
        Frequent Visitor
        4 years ago

        I don't know why it is not working. I have a huge data set with a lot of dates and they are not necessarily consequitive days. Some has one week or two weeks between them.  I have also 1000 "fruits" on my table. 

        I downloaded the sample you made, it looks fine but I suspect it has something to do with filters, or dates. I am not sure. Tom's answer is good but like I mentioned, I have too many items to track on a ribbon chart. 

         

        Do you think you can make adjustments so instead of filtering to find today's date, I would like to be able to choose two dates from a filter or something so I can compare two specific dates. 

         

        Ps. I have a dedicated Calendar table as many of you suggested.