DAX QUERY FOR FILL DOWN

[Anonymous]

Hi Professionals..!!

Need help in understanding the dax for getting the last column data as measure, basically it is a fill down in dax.

[Jihwan_Kim · Accepted solution]

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 )

[Anonymous]

https://drive.google.com/file/d/1CMFr0oRjq1rb8gdCas-5V5oEa_s606uW/view 

@Jihwan_Kim please help us with this data as we are unable to get the correct result

[Jihwan_Kim · Accepted solution]

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

[Anonymous]

@Jihwan_Kim @tamerj1 @amitchandak @SpartaBI 

[Jihwan_Kim · Accepted solution]

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 )