Calculate difference of items between date and date of previous occurance

[kovacikm78]

Hi guys,
i would like to ask you for a help.
I have  a callendar table and another table with itemID, date, cathegory and sales.
I would like to show a chart or a table showing by each date change of number of itemIDs to previous occurance.
I am using this measure to count number of items: 

DISTINCTCOUNT('SellingHistory'[itemID])
E.g.: 06-28-2023 I sold 1000 items and previous, on 06-29-2023 I sold none and on 06-30-2023 i sold 1200 items.
On the chart or in the table I want to show by 06-30-2023 number: 20% change
Can someone please help me to create such measure? I would need to show this measure by each cathegory. 
Thank you for your help

[Ritaf1983 · Accepted solution]

Hi @kovacikm78 

You can achieve your goal with 2 steps (you can  combine them to 1 measure i split it to make simple).

1. calculate the previous date with measure:

previous_Date_units_sold =

if(ISBLANK([Units_Sold]),BLANK(),CALCULATE([Units_Sold],

CALCULATETABLE(LASTNONBLANK('Calendar'[Date],CALCULATE([Units_Sold])),

DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),min('Calendar'[Date])-1))))

2. calculate the percantage change

perecent_Change = if (ISBLANK([previous_Date_units_sold]),blank(),[Units_Sold]/[previous_Date_units_sold])

It will also work with slicers

Link to the sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[kovacikm78 · Accepted solution]

Hello @Ritaf1983 ,

thx again for your help. I have another question for further adjustement.

Is there a way to aggregate this measure to weeks. Lets say I want to see the percent change to previous week. I have a calendar table with week/year column.
Your help is much appreciated

[kovacikm78]

thank you very much for you help

[Ritaf1983]

Happy to assist 😊

[kovacikm78 · Accepted solution]

Hello @Ritaf1983 ,

thx again for your help. I have another question for further adjustement.

Is there a way to aggregate this measure to weeks. Lets say I want to see the percent change to previous week. I have a calendar table with week/year column.
Your help is much appreciated

[Ritaf1983 · Accepted solution]

Hi @kovacikm78 
Yes, please refer to the linked tutorial:

https://www.youtube.com/watch?v=7_Q5yaU-Fjc

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi @kovacikm78 

You can achieve your goal with 2 steps (you can  combine them to 1 measure i split it to make simple).

1. calculate the previous date with measure:

previous_Date_units_sold =

if(ISBLANK([Units_Sold]),BLANK(),CALCULATE([Units_Sold],

CALCULATETABLE(LASTNONBLANK('Calendar'[Date],CALCULATE([Units_Sold])),

DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),min('Calendar'[Date])-1))))

2. calculate the percantage change

perecent_Change = if (ISBLANK([previous_Date_units_sold]),blank(),[Units_Sold]/[previous_Date_units_sold])

It will also work with slicers

Link to the sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly