Forum Discussion

nbufff's avatar
nbufff
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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
Icon for Helper I rankHelper I
1 year ago

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

 

thank for advise.

 

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    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,