Forum Discussion

nbufff's avatar
nbufff
Helper I
1 year ago
Solved

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 wi...
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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.

     

     

    I have attached an example pbix file for your reference.

     

    Best regards,

DataNinja777's avatar
DataNinja777
Super User
1 year ago

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:

The resulting output will look like as follows:

I have attached an example pbix file for your reference.

 

Best regards,

 

 

 

nbufff's avatar
nbufff
Helper I
1 year ago

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