Forum Discussion

penningmic's avatar
penningmic
Frequent Visitor
5 years ago
Solved

Get value for specific data from measure then categorize

Hello,  I have a dataset where I previously adapted code from my following question: lastnonblank - relating 2 tables via measure to get value Producing an output like this (Example, not synced w...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi penningmic ,

     

    According to my understanding, you want to get the change between the first and the last value of each Item and calculate the number of items under each value change, right?

     

    You could try this to add a column to DataTable:

    Type =
VAR _mindate =
    CALCULATE (
        MIN ( 'DataTable'[Created Date] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[Item_Num] )
    )
VAR _fir =
    MINX (
        FILTER (
            'DataTable',
            'DataTable'[Item_Num] = EARLIER ( 'DataTable'[Item_Num] )
                && 'DataTable'[Created Date] = _mindate
        ),
        [Value]
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'DataTable'[Created Date] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[Item_Num] )
    )
VAR _last =
    MAXX (
        FILTER (
            'DataTable',
            'DataTable'[Item_Num] = EARLIER ( 'DataTable'[Item_Num] )
                && 'DataTable'[Created Date] = _maxdate
        ),
        [Value]
    )
RETURN
    _fir & " to " & _last

    The final output is shown below:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi penningmic ,

 

According to my understanding, you want to get the change between the first and the last value of each Item and calculate the number of items under each value change, right?

 

You could try this to add a column to DataTable:

Type =
VAR _mindate =
    CALCULATE (
        MIN ( 'DataTable'[Created Date] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[Item_Num] )
    )
VAR _fir =
    MINX (
        FILTER (
            'DataTable',
            'DataTable'[Item_Num] = EARLIER ( 'DataTable'[Item_Num] )
                && 'DataTable'[Created Date] = _mindate
        ),
        [Value]
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'DataTable'[Created Date] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[Item_Num] )
    )
VAR _last =
    MAXX (
        FILTER (
            'DataTable',
            'DataTable'[Item_Num] = EARLIER ( 'DataTable'[Item_Num] )
                && 'DataTable'[Created Date] = _maxdate
        ),
        [Value]
    )
RETURN
    _fir & " to " & _last

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

penningmic's avatar
penningmic
Frequent Visitor
5 years ago

Hi Anonymous,

Thank you again for your response and assistance. 

This is very close, however, I need to be able to specify the minDate as a specific date, say Jan 1. and I would like to populate that value with the lastnonblank value like what the measure is doing.  Technically, I would need to do the same for an end date, but the pattern should be identical.

In the example - the result would look the same, but my actual data set goes back years.  So, when I start with the minDate value, I could missing changes inbetween.  If the value change at beginning of 2019 to a "1" and a "2" at the end of 2019, I want to look at it in beginning 2021 on Jan 1., I still want to return a "2".

 

I think that would require use of the lastnonblank and a calendar table

Calendar = Calendar(Date(2020,1,1),Date(2021,12,31)

I have been unsuccessful trying to modify your code to accept a date or get a lastnonblank value.

A quick comment on your graphic, you want to count the distinct Item_Num's rather than the Type.  And - if you would humor me, I am trying to breakdown your formula so that I can understand it and learn from it.  You have 2 repeating patterns, so trying to talk through 1 of them.

VAR _mindate =
    CALCULATE (
        MIN ( 'DataTable'[Created Date] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[Item_Num] )
    )

//The ALLEXCEPT returns a virtual table of the unique Item_Num's (basically groups the Item_Num) and then we look for the MIN dates assocaited with each unique Item_Num?

VAR _fir =
    MINX (
        FILTER (
            'DataTable',
            'DataTable'[Item_Num] = EARLIER ( 'DataTable'[Item_Num] )
                && 'DataTable'[Created Date] = _mindate
        //Now, I am filtering to return a table where I compare the previous row (or all previous rows?) to the current row (This is doing grouping so I am only comparing "Item 1" with "Item 1"?) && where the Created Date for that particular distinct Item_Num, "Item_1", is equal to the minimum date in the previous variable, which is a table of Dates and associated Item_Num),
        [Value]
//This is just the expression - return the max Value from the DataTable for each line.
    )

 

Thanks,
Mike