Getting Previous Row using DAX measure

Hi Jihwan_Kim 
Thankyou for this measure but if you see at the last row of the table visual it is showing some value, but I need it to be 0 rather than some value because from that time pump is started to value would be zero. 

So I modified the formula using IF condition, to do that, but there is a issue in grand total it is still showing the same value as it was showing before rather than summing the value of records in this column. 

I have used this measure:

Hi,

Thank you for your feedback.

Perhaps, I do not fully understand about what the data is saying.

On grand total row, do you want to show zero? Or, blank?

If it is zero, may I ask why is it zero?

Hi Jihwan_Kim 
Sorry I think I was not able to explain you clearly.

I was saying I want to see 0 on the last record of the table not on the grand total, and I have done that by modifying the measure. You can see the screenshot

But now issue is, in the grand total of Test Measure it is not summing the values of that column it is showing some different value.

I am confused in this issue and I would be really greatful if it is resolved 🙂

Thanks and Regards
Mihir

Hi,

May I ask what is the number that you want to see on Grand total?

Hi Jihwan_Kim 

I don't know the actual value, but I need the sum of all the values in Test Measure.

Thanks and Regards 

Mihir

Hi,

Could you please check the attached file?

I tried to create measures again, and please check the last measure = [test measure total fix] if it shows a correct number for the Grand Total row.

Please be noted that the DAX formula might be slow. I will try to find a way to optimize the DAX formula, but please check whether the number on the grand total level for Test Measure Total Fix is correct.

Thanks.

Hi Jihwan_Kim 
Thanks for the Measure it is showing correct value... but yeah it is working slow, and in my original data there are millions of rows and there it is taking so much time to execute on the visual and after that showing error :

Can we optimize this measure or we should for look for another approach ?

Thanks and Regards
Mihir

Hi,

Please check the attached pbix file.

All measures are in the attached pbix file. It is still slow, but I it is faster than before.

Could you please check if it works for your datamodel?

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:

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

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

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