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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
nbufff
Helper I
Helper I

Show previous date data on matrix table

Dear Community,

I stuck on issue how to show previous date data on matrix table. 

 

I made matrix table showing qty each day from plant A and B. I want to make it as  showing in  second picture with high-lighting the part in yellow.  Basically 11/3 is missing so it needs to be added by using the data from 11/2.  Also Plant A 11/6 qty should be from 11/5 and plant B 11/2 data to be from 11/1. How can we make it? Thanks for help! 

P.png

p2.png

1 ACCEPTED SOLUTION

Hi @nbufff ,

 

You can produce your required output by writing a dax measure like below:

 

Date_Diff = 
VAR StartDate = DATE(2024, 11, 1)  -- Starting point
VAR CurrentDate = SELECTEDVALUE(Calendar[Date])
RETURN
IF(
    CurrentDate >= StartDate,
    CALCULATE(
        COUNTROWS('Calendar'),
        FILTER(
            ALL('Calendar'),
            Calendar[Date] >= StartDate &&
            Calendar[Date] <= CurrentDate &&
            Calendar[IsWorkingDay] = 1
        )
    ),
    BLANK()  -- If the date is before November 1, return blank
)

 

The resulting output is shown below. I've transposed the visualization to save space.

 

DataNinja777_1-1731640802041.png

 

I have attached an example pbix file for your reference.

 

Best regards,

View solution in original post

5 REPLIES 5
DataNinja777
Super User
Super User

Hi @nbufff ,

 

You can produce your required output by writing a dax measure like below:

CarryOverQty = 
VAR CurrentDate = SELECTEDVALUE(Calendar[Date])
VAR PreviousQty =
    CALCULATE(
        MAXX(
            FILTER(
                ALL('Calendar'),
                Calendar[Date] < CurrentDate && NOT(ISBLANK([Quantity]))
            ),
            [Quantity]
        )
    )
RETURN
IF(
    ISBLANK([Quantity]),
    PreviousQty,
    [Quantity]
)

The data model will look like below:

DataNinja777_0-1731591700206.png

The resulting output will look like as follows:

DataNinja777_1-1731591747593.png

I have attached an example pbix file for your reference.

 

Best regards,

 

 

 

@DataNinja777 , I tried your formual in my case. it works on most data  but some data seemed weir.

 

from below screen you can see on 11/21 there's carryover data 52550 which accordig to your method should be 1800 since actually there's no data on that day.

 

There's few other colum also encountered such issue.

 

I also show you the table view screen shot for your reference. Pls kindly advise if I can improve.

 

 

 

carryover_mavqty = 
VAR currentdate=SELECTEDVALUE(Calendar_Table[Date])
VAR previousqty =
    CALCULATE(
        MAXX(
            FILTER(
                ALL(Calendar_Table),
                Calendar_Table[Date] < currentdate && NOT(ISBLANK([sum_mavqty]))
            ),
            [sum_mavqty]
        )
    )
RETURN
IF(
    ISBLANK([sum_mavqty]),previousqty,[sum_mavqty])
 

 

 

nbufff_0-1731658253440.png

nbufff_1-1731658425650.png

 

 

@DataNinja777 , It works but the situation here little bit complex.

we need to by pass the holdaiy and get the date difference between working days.

e.g. we don't need to show Nov.2 and Nov.3 but we need to show working day difference from Nov.1

 

nbufff_0-1731597587643.png

thank for advise.

 

Hi @nbufff ,

 

You can produce your required output by writing a dax measure like below:

 

Date_Diff = 
VAR StartDate = DATE(2024, 11, 1)  -- Starting point
VAR CurrentDate = SELECTEDVALUE(Calendar[Date])
RETURN
IF(
    CurrentDate >= StartDate,
    CALCULATE(
        COUNTROWS('Calendar'),
        FILTER(
            ALL('Calendar'),
            Calendar[Date] >= StartDate &&
            Calendar[Date] <= CurrentDate &&
            Calendar[IsWorkingDay] = 1
        )
    ),
    BLANK()  -- If the date is before November 1, return blank
)

 

The resulting output is shown below. I've transposed the visualization to save space.

 

DataNinja777_1-1731640802041.png

 

I have attached an example pbix file for your reference.

 

Best regards,

johnt75
Super User
Super User

Try

Including Missing Dates =
COALESCE (
    [Base Measure],
    VAR MaxDate =
        MAX ( 'Date'[Date] )
    VAR Result =
        LASTNONBLANKVALUE (
            FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] < MaxDate ),
            [Base Measure]
        )
    RETURN
        Result
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.