DAX QUERY FOR FILL DOWN

Hi,

Please check the below and the attached pbix file.

Those are for both creating a measure and a calculated column.

 

Desired measure: = 
VAR currentrows =
    MAX ( Data[Rows] )
VAR currentdate =
    MAX ( Data[Date] )
VAR startdate =
    MINX (
        FILTER ( ALL ( Data ), Data[Rows] = currentrows && Data[NewMC] <> BLANK () ),
        Data[Date]
    )
VAR previousdate =
    MAXX (
        FILTER (
            ALL ( Data ),
            Data[Rows] = currentrows
                && Data[Date] <= currentdate
                && Data[NewMC] <> BLANK ()
        ),
        Data[Date]
    )
VAR previousvalue =
    MAXX (
        FILTER ( ALL ( Data ), Data[Rows] = currentrows && Data[Date] = previousdate ),
        Data[NewMC]
    )
RETURN
    IF (
        HASONEVALUE ( Data[Rows] ),
        IF ( MAX ( Data[Date] ) = startdate, SUM ( Data[NewMC] ), previousvalue )
    )

 

Desired CC = 
VAR currentrows = Data[Rows]
VAR currentdate = Data[Date]
VAR startdate =
    MINX (
        FILTER ( Data, Data[Rows] = currentrows && Data[NewMC] <> BLANK () ),
        Data[Date]
    )
VAR previousdate =
    MAXX (
        FILTER (
            Data,
            Data[Rows] = currentrows
                && Data[Date] <= currentdate
                && Data[NewMC] <> BLANK ()
        ),
        Data[Date]
    )
VAR previousvalue =
    MAXX (
        FILTER ( Data, Data[Rows] = currentrows && Data[Date] = previousdate ),
        Data[NewMC]
    )
RETURN
    IF ( Data[Date] = startdate, Data[NewMC], previousvalue )

 

Hi,

Your pbix file has a page-filter.

Please try the below and check the attached file.

 

Mydesire = 
VAR currentrows =
    MAX ( Mydata[Rows] )
VAR currentdate =
    MAX ( Mydata[Date] )
VAR startdate =
    MINX (
        FILTER ( ALLSELECTED( Mydata ), Mydata[Rows] = currentrows && Mydata[NewMC] <> BLANK () ),
        Mydata[Date]
    )
VAR previousdate =
    MAXX (
        FILTER (
            ALLSELECTED ( Mydata ),
            Mydata[Rows] = currentrows
                && Mydata[Date] <= currentdate
                && Mydata[NewMC] <> BLANK ()
        ),
        Mydata[Date]
    )
VAR previousvalue =
    MAXX (
        FILTER ( ALLSELECTED ( Mydata ), Mydata[Rows] = currentrows && Mydata[Date] = previousdate ),
        Mydata[NewMC]
    )
RETURN
    IF (
        HASONEVALUE ( Mydata[Rows] ),
        IF ( MAX ( Mydata[Date] ) = startdate, SUM ( Mydata[NewMC] ), previousvalue )
    )