Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table with Plant Stock (column “Stock”) for certain dates (column “Calendar Day”). I am looking for a measure to calculate the difference of the latest day vs. the day before. I setup following measure which works nearly perfectly:
Change = sum(Actuals[Stock]) - calculate(sum(Actuals[Stock]) , dateadd ('Actuals'[Calendar day], -1 , Day ))
However the problem is that there might be bank holidays or weekends where I do not have yesterdays date and the offset of the above formula of 1 day will not have any result.
I am looking for a flexible formula which gives me the sum of the 2nd latest day of the table (can be yesterday, the day before yesterday or even 3 days ago…) and comparing to the most recent day.
Any ideas?
Solved! Go to Solution.
Hi @joamen ,
If the weekend has now row content, we can use the following measure to meet your requirement.
Change = SUM ( 'Table'[Stock] ) - SUMX ( FILTER ( ALL ( 'Table' ), [Calendar day] = MAXX ( FILTER ( ALL ( 'Table' ), [Calendar day] < MAXX ( FILTERS ( 'Table'[Calendar day] ), [Calendar day] ) ), [Calendar day] ) ), [Stock] )
If the weekend day just mean stock column is zero or null, you can use the following measure
Change2 = var result = SUM ( 'Table2'[Stock] ) - SUMX ( FILTER ( ALL ( 'Table2' ), [Calendar day] = MAXX ( FILTER ( FILTER(ALL ( 'Table2' ),AND ( [Stock] <> 0, NOT ISBLANK ( [Stock] ) )), [Calendar day] < MAXX ( FILTERS ( 'Table2'[Calendar day] ), [Calendar day] ) ), [Calendar day] ) ), [Stock] ) return IF(SUM([Stock])=0,BLANK(),result)
If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hi @joamen ,
If the weekend has now row content, we can use the following measure to meet your requirement.
Change = SUM ( 'Table'[Stock] ) - SUMX ( FILTER ( ALL ( 'Table' ), [Calendar day] = MAXX ( FILTER ( ALL ( 'Table' ), [Calendar day] < MAXX ( FILTERS ( 'Table'[Calendar day] ), [Calendar day] ) ), [Calendar day] ) ), [Stock] )
If the weekend day just mean stock column is zero or null, you can use the following measure
Change2 = var result = SUM ( 'Table2'[Stock] ) - SUMX ( FILTER ( ALL ( 'Table2' ), [Calendar day] = MAXX ( FILTER ( FILTER(ALL ( 'Table2' ),AND ( [Stock] <> 0, NOT ISBLANK ( [Stock] ) )), [Calendar day] < MAXX ( FILTERS ( 'Table2'[Calendar day] ), [Calendar day] ) ), [Calendar day] ) ), [Stock] ) return IF(SUM([Stock])=0,BLANK(),result)
If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
BTW, pbix as attached.
Best regards,
Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more
Hey @v-lid-msft,
indeed your provided formula (first one) is giving the right output in case always the weekends are missing. Many thanks.
However it is not 100% fixing my issue in case there are for example 3 days or more missing...
I thought about duplicating the table with the calendar day and only have the column calendar day in it. Then removing the latest day and only showing the latest date what is in the table in this new query to have the 'previous day' ... with this I could imagine that there might be a formula to calculate the difference of 'latest date' vs this 'previous day' from this new query. Do you think this might work?
Thx
Hi @joamen ,
The first formula can work fine with over 3 days missed, you can see that 9/13 - 9/15 are missed in my example data.
if you just want the difference between the latest day and the day before, not all the differentce. You can use the following measure.
LastDayChanges = CALCULATE([Change],LASTDATE(ALL('Table'[Calendar day])))
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @joamen
Time intelligence functions without a complete calendar date can give rise to unexpected behavior and it is not recommended. The correct behaviour is only guaranteed with full calendar years (no gaps). It's probably best to create a calendar table that specifies the weekend and holidays and use that.
I think you can create another column called "Trading Day" and mark the record if it is a trading day. Then, you can use the formula you use to calculate the changes vs last day.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |