Last known previous value in unpivoted data

[Anonymous]

I've seen variants of this question asked by different people, but could never find a solution which made sense.  Perhaps I'm just wording myself incorrectly?  If so, please direct me to the nearest tutorial on this topic and I'll happily read it.

I have, a given date, a given platform, and a given category.  Based on those three parameters, a count is made of issues which have been closed.  For the given platform, and given category, I want to get (I guess in DAX?) the last known previous value of the Count of issues closed.

The time betweeen dates can be a year or a day, ideally even a second.  I should be able to calculate, based on the information in Platform and Category, what the value is for the last known data point previous to the current date.  I don't belive I can just use something like 'previousday', because I don't care about the day literally previous to 14/12/2018 (so 13/12/2018).  I care about the last known data point, which in this case is on the day 12/12/2018.

This seems maddeningly simple, and I feel like I'm just not using the right keywords to search on this topic.

Any pointers would be appreciated.

[Anonymous]

Thank you both for your replies.  Conceptually the solution is simple, but the formulas slightly more complicated than I expected 😄

@Vvelarde 

I'm using PowerPivot in Excel, so unfortunately I can't use the SELECTVALUE function.  Your code looks a little more elegant, and I guess is basically the same idea as the Nick_M's solution, though it's not completely clear to me why MAX is used.

@Anonymous 
I've selected your answer as the solution.  I had thought about using an Index before, but gave up on that idea because it needed to be for multiple criteria.  Apparently though that's possible, something I didn't realize.  That's something to use more often in the future probably.  Now I just need to figure out how to do it in M language .
All of the logic is clear, but I want to get the results as a calculated column instead of as measure.  I plan to, based on the index, sum up all 'Count of Previous Issues' including the current and all previous lines(dates) for each platform/category combination.  So I get a sort of running total, based on multiple critera.

I tried a couple of amateurish ways to get the calculated column, but neither of them work.

Seems really close to what I want...

=
Var __CurrentPlatform = 'Table2'[Platform]
Var __CurrentCategory = 'Table2'[Category]

RETURN
CALCULATE( 
        SUM('Table2'[Count Issues Closes]), 
            FILTER( 
                ALL ('Table2'),
                    'Table2'[Platform]=__CurrentPlatform
                    &&'Table2'[Category]=__CurrentCategory
                    &&MAX( 'Table2'[Index])-1 = 'Table2'[Index]
            ), 
    )

My second attempt seems really wrong haha...

=
Var __CurrentPlatform = 'Table2'[Platform]
Var __CurrentCategory = 'Table2'[Category]

RETURN
CALCULATE( 
        SUM('Table2'[Count Issues Closes]), 
            FILTER( 
                ALL ('Table2'),
                    MAX( 'Table2'[Index])-1 = 'Table2'[Index]
            ), 
        VALUES('Table2'[Platform]), 
        VALUES('Table2'[Category])
    )

 Do I just need to make a small adjustment to one of the above formulas?

[Anonymous]

@Anonymous 

Can you upload a small sample of the table where you are trying to create these columns?

Also, in the other formula from @Vvelarde , since you are returning only 1 row you can use any aggregator, he just happened to choose max. 

[Anonymous]

Hi @Anonymous 

I've uploaded a pbix file with some sample data and the current solution (changed some of the column names a bit).
Basically, right now I have the index as you've provided, and I want to get a sum of all previous values directly in a column inside the table. 
I've highlighted in the screenshot below where the value in 'Previous Values' is currently coming from.  The 'Previous Values' column—an attempt at modifying your 'Count of Previous Issues' measure to be for a calculated column—creates only a single value, for the matching Platform 38, Category 16.  Link to sample data is in the image.

Previous Values = 
Var __CurrentPlatform = 'SQL Data'[Platform]
Var __CurrentCategory = 'SQL Data'[Category]

RETURN
CALCULATE( 
        SUM('SQL Data'[Number of Records Closed (per day)]), 
            FILTER( 
                ALL ('SQL Data'),
                    'SQL Data'[Platform]=__CurrentPlatform
                    &&'SQL Data'[Category]=__CurrentCategory
                    &&MAX( 'SQL Data'[Previous Value Index])-1 < 'SQL Data'[Previous Value Index]
            ) 
    )

I could guess that my filtering is incorrect, but do the variables also need to be defined in a different way because this is a calculated column?


My end goal for this is to create a final calculated column being simply

Currently Open Records = 'SQL Data'[All existing open/closed points per Platform & Category] - 'SQL Data'[Previous Values]

And this way there would be a sort of timeline.

[Anonymous]

See if this is what  you had in mind:

if so, dont need the index column anymore. 

Previous Values = 
Var __CurrentPlatform = 'SQL Data'[Platform]
Var __CurrentCategory = 'SQL Data'[Category]
Var __CurrentDate = 'SQL Data'[Date]

RETURN
CALCULATE( 
        SUM('SQL Data'[Number of Records Closed (per day)]), 
            FILTER( 
                'SQL Data',
                    'SQL Data'[Platform]=__CurrentPlatform
                    &&'SQL Data'[Category]=__CurrentCategory
                    && 'SQL Data'[Date] <= __CurrentDate
            ) 
    )

[Anonymous]

This is exactly what I was trying to figure out.  And it's more in-line with the 'easy' factor I thought it should be.

Thanks a lot.  Although it wasn't necessary for my end use, that index idea is still going to be quite useful in the future I think.  I'm kind of surprised I couldn't find this kind of info already 'out there' though.

The solution you present is basically a running total, right?  But then it's modified by looking at a couple of factors.  Any sort of generic name for this with regards to DAX?

[Anonymous]

So this is the monstrosity I managed to create with the help of Nick_M's solutions.
I had to relate the information to a date table, do some sorting, and fill down data.  But otherwise that's it (although another condition has to be fulfilled for the fill to work properly).  Probably will ask for critique of the fill approach later in another thread...

In any case, thanks Nick_M 

[Anonymous]

@Anonymous  

[Anonymous · Accepted solution]

Hopefully this will help 🙂

1. Need an index column in your table so we know what "previous" means. Can do this in Power Query or a DAX Calculated column. I went the Dax route here. You would add a new column to your table using this code:

Index = 
Var __CurrentDate = Table2[Date]
Var __CurrentPlatform = Table2[Platform]
Var __CurrentCategory = Table2[Category]

RETURN
CALCULATE(
    COUNTROWS( Table2 ),
    FILTER( 
        ALL( Table2 ),
        __CurrentPlatform = Table2[Platform]
        && __CurrentCategory = Table2[Category]
        && __CurrentDate >= Table2[Date]
    )
)

which gives you this table ( I added a few records...)

Now we have an idea of what previous is. So we create these measures:

Count of Issues = SUM ( Table2[Count Issues Closes] )

Count of Previous Issues = 
    CALCULATE( 
        [Count of Issues], 
            FILTER( 
                ALL (Table2), 
                    MAX( Table2[Index])-1 = Table2[Index]
            ),                , 
        VALUES( Table2[Category]), 
        VALUES( Table2[Platform])
    )

Which produces this output:

[Vvelarde]

@Anonymous 

Hi, lets try with this measure:

PreviousCountofIssuesClosed =
VAR _Date =
    SELECTEDVALUE ( Table4[Date] )
RETURN
    CALCULATE (
        MAX ( Table4[Count of issues closed] );
        TOPN (
            1;
            FILTER (
                ALLEXCEPT ( Table4; Table4[Platform]; Table4[Category] );
                Table4[Date] < _Date
            );
            Table4[Date]; DESC
        )
    )

Regards

Victor