Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sertan_CPH
Frequent Visitor

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.

 

Now show me how you will create a visual that shows what changed between these dates? Now imagine how to show the change trackign for the whole year. Can you help?

 

DateFruitPrice
17-09-2021Apple10
17-09-2021Orange15
17-09-2021Banana20
17-09-2021Pear25
18-09-2021Apple15
18-09-2021Banana22
18-09-2021Pear25
8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table with a relationship from the Date column of your Data Table to the Date column of your Calendar Table.  To your visual, drag the Date column from your Calendar Table and Fruit column from your Data Table.  Write these measures

Total price = sum(data[price])

Total price on previous day = calculate([total price],previousday(calendar[date]))

Growth (%) = divide(([total price] - [Total price on previous day]),[Total price on previous day])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 Hmm, weird, I actually tested that one. See attached PBIX beneath signature. I have to say though, I like @TomMartens approach.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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. 

TomMartens
Super User
Super User

Hey @Sertan_CPH ,

 

I consider the Ribbon chart a good fit for this data visualization task:

image.png

In regards to visualizing a complete year, I recommend creating a dedicated Calendar table, and create a relationship between the Calendar table (the one-side) and your table (the many-side). Then you can use the Calendar table on the x-axis of the ribbon chart.

This article also contains how to create a Calendar table: Time patterns – DAX Patterns

 

Hopefully, this helps to provide some ideas on how to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens  Hi

 

It is very practical and I really like this method but as you can imagine my data is very large and not only few fruits but 1000 line items. It is simply too confusing to use ribbon chart in this case

Greg_Deckler
Super User
Super User

@Sertan_CPH For part of the answer, you can use MTBF, which will help you show changes between days as long as items exist. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.