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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
miiihiir
Helper II
Helper II

Getting Previous Row using DAX measure

Hello all, 

I need to get previous record based on DateTime column and grouped by TagIndex column

 

I created this measure,

 

Prev_value_Energy_consum =
VAR Index = [Index]
VAR Prev_date =
    MAXX(FILTER(
        ALL(FloatTable[Time]),
        FloatTable[Time] < SELECTEDVALUE(FloatTable[Time])
        ),
    FloatTable[Time]
    )
RETURN
CALCULATE(SUMX(FloatTable,
    VAR Index = FloatTable[TagIndex]
    RETURN
    FloatTable[Val]), FILTER(ALL(FloatTable), FloatTable[Time] = Prev_date && FloatTable[TagIndex] = Index ))
 
everything is going perfect, but on some dates this is giving blank rows....IDK why?
 
miiihiir_0-1664279403723.png

 


Can anyone help me?

Thanks and Regards
Mihir
1 ACCEPTED SOLUTION

Hi,

Thank you for your sharing.

Could you please try the below if it works?

 

Prev_value_Energy_consum =
VAR Index = [Index]
VAR Prev_datetime =
    MAXX (
        FILTER (
            ALLSELECTED ( FloatTable ),
            FloatTable[Date] = SELECTEDVALUE ( FloatTable[Date] )
                && FloatTable[Time] < SELECTEDVALUE ( FloatTable[Time] )
        ),
        FloatTable[Time]
    )
VAR Prev_date =
    MAXX (
        FILTER (
            ALLSELECTED ( FloatTable ),
            FloatTable[Date] < SELECTEDVALUE ( FloatTable[Date] )
        ),
        FloatTable[Date]
    )
VAR Prev_time =
    MAXX (
        FILTER ( ALLSELECTED ( FloatTable ), FloatTable[Date] = Prev_date ),
        FloatTable[Time]
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ISBLANK ( Prev_datetime ),
            CALCULATE (
                SUMX ( FloatTable, VAR Index = FloatTable[TagIndex] RETURN FloatTable[Val] ),
                FILTER (
                    ALLSELECTED ( FloatTable ),
                    FloatTable[Time] = Prev_datetime
                        && FloatTable[Date] = SELECTEDVALUE ( FloatTable[Date] )
                        && FloatTable[TagIndex] = Index
                )
            ),
        CALCULATE (
            SUMX ( FloatTable, VAR Index = FloatTable[TagIndex] RETURN FloatTable[Val] ),
            FILTER (
                ALLSELECTED ( FloatTable ),
                FloatTable[Time] = Prev_time
                    && FloatTable[Date] = Prev_date
                    && FloatTable[TagIndex] = Index
            )
        )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

23 REPLIES 23

Hi @Jihwan_Kim 

Thanks for your measure @Jihwan_Kim but there was a small issue with that measure. 


Instead I have created this new file with the help of some of your measures, these measures are working fine in my data model also(and it is pretty fast) but there is the last issue remaining which is similar to the meaure which you've created.


So in this file (which I have shared) there is a meaure for Previous value. Issue is whenever we filter the date using slicer, then at the last row of the visual it is showing blank value (logically it is correct because when filter is applied then, there is not any previous value) but by modifying this measure can we do something to get that previous value also.


I tried your previous measures for this issue but those measure were taking so much time to load, so for now I'm looking for a way to modify this (current) measure so that I can also get previous value for the last row of the table.


Here are some screenshots:

miiihiir_0-1664960088792.png

 

when I am not selecting any date then it is showing previous value like you can see in the below screenshot :

 

miiihiir_1-1664960140778.png

 

So I just want to know from your expertise can we do some small modification in this measure and rectify this issue ?


If you have any questions feel free to ask 🙂


File Link: https://drive.google.com/drive/u/0/folders/1651tuMrez13NX48mrdh_UVSZuk7diGMP

 

Thanks and Regards
Mihir

 

Hi,

Can you try the below measure?

I am not sure about the performance, but I tried to fix the measure that can show the previous value.

 

Prev_value_Energy_consum1 =
VAR Index =
    MAX ( FloatTable[TagIndex] )
VAR Prev_datetime =
    MAXX (
        FILTER (
            ALL ( FloatTable ),
            FloatTable[Date] = MAX ( FloatTable[Date] )
                && FloatTable[Time] < MAX ( FloatTable[Time] )
                && FloatTable[TagIndex] = Index
        ),
        FloatTable[Time]
    )
VAR Prev_date =
    MAXX (
        FILTER (
            ALL ( FloatTable ),
            FloatTable[Date] < MAX ( FloatTable[Date] )
                && FloatTable[TagIndex] = Index
        ),
        FloatTable[Date]
    )
VAR Prev_time =
    MAXX (
        FILTER (
            ALL ( FloatTable ),
            FloatTable[Date] = Prev_date
                && FloatTable[TagIndex] = Index
        ),
        FloatTable[Time]
    )
RETURN
    SWITCH (
        TRUE (),
        NOT ISBLANK ( Prev_datetime ),
            SUMX (
                FILTER (
                    ALL ( FloatTable ),
                    FloatTable[Time] = Prev_datetime
                        && FloatTable[Date] = MAX ( FloatTable[Date] )
                        && FloatTable[TagIndex] = Index
                ),
                FloatTable[Val]
            ),
        CALCULATE (
            SUMX (
                FILTER (
                    ALL ( FloatTable ),
                    FloatTable[Time] = Prev_time
                        && FloatTable[Date] = Prev_date
                        && FloatTable[TagIndex] = Index
                ),
                FloatTable[Val]
            )
        )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thanks for your measure @Jihwan_Kim, but it is very slow... 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors