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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joamen
New Member

Change vs last available day

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?

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

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]
    )

8.5.PNG9.PNG

 

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)

10.PNG

 

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 

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.

View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
Community Support

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]
    )

8.5.PNG9.PNG

 

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)

10.PNG

 

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 

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.

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])))

1.PNG

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.

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.
AlB
Super User
Super User

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.

 

matthewtian777
Frequent Visitor

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.