Get value for specific data from measure then categorize

[penningmic]

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 with table below)

	Month1	Month2	Month3	Month4
Item 1	0	0	1	1
Item 2		1	1	1
Item 3	1	1	0	1
Item 3	2	1	1	1

That measure looks like the following:

LastnonBlank2 = 
var currentdate = max(CalendarTable[Date])
return
CALCULATE (
            LASTNONBLANKVALUE (
              Record[CreatedDate],
                max (Record[Value])
            ),
            filter(allexcept(Record,Record[Item_Num]),
            Record[CreatedDate] <= CURRENTdate)
        )

Now, I need I would like to be able to count combinations of:

how many started as a 0 and ended as a 1

1's --> 2's

2's --> 1's

etc.  

I have a calendar table (just autocalendar for 2020-21) and a DataTable: that looks something like this:

Created Date	Value	Item_Num
12/1/2020 0:00	0	Item 1
1/1/2021 0:00	0	Item 2
1/3/2021 0:00	2	Item 2
1/25/2021 0:00	1	Item 2
1/5/2021 0:00	1	Item 3
1/23/2021 0:00	0	Item 3

I am not sure what is the best approach here.  At my current understanding, I think I need to create calculated columns for what the value is on Day 0 (in the example Jan 1.) and the end or quarter (or any other day I want to compare).  Then, I would use an if statement to determine the classification - started as a "1" Day0 --> "2" end of Q.  Then, I can count those occurances.  All calculated columns (I think I may need to filter on these subsets).

The challenge I am having is how to get the "Value" for a day that isn't in my DataTable but is on my calendar.  Items 1 doesn't have a entry for Jan1, but I need to get the "1" Value since it hasn't changed.  Item 2 has values on Jan1, so that one would be fine. Item 3 would show up as blank.   The value appears in my measure that I showed at the top, but don't know how to get a value from a specific date in my measure for Jan1 or any other specific date.  Then, I think I can apply the logic/IF statements to get the classifications.

I don't know where to even start with manipulating the measure to a calculated column for a specific date unfortunately...

Sorry, cannot create/share files through org.

Thanks for any thoughts or suggestions.  Any logic/description that you provide with the expression would be greatly appreciated as I am trying to learn rather than just get answers.

Mike

[v-eqin-msft · Accepted solution]

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]

Hi @v-eqin-msft,

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

[v-eqin-msft]

Hi @penningmic ,

Sorry for that the information you have provided is not making the problem clear to me...

Could you provide me with more details about your table and your problem or build a data sample since you could not share the pbix file?

Best Regards,
Eyelyn Qin

[penningmic]

Hi @v-eqin-msft,

I was able to make some progress, please see below and more detail to help answer your question.  Thanks again for your response.  Not letting me add tables and reply, so just sending pics, tables in original post.

I have a calendar table (just autocalendar for 2020-21) and a DataTable: that looks something like this:

I would like to count the number of items that started with a value of "1" on Jan 1. and ended with a value of "1" on say, Feb 1st.  Then, I would like to count number of items that started as value of 2 and ended with a value of 1 on Feb 1st.   

I was thinking over the weekend that I could modify the sample to use something that specified the dates, like the code below.  

lastnonblank call jan 1 = 
VAR currentdate =
    MAX ( Dates[Date] )
var Day0 = DATE(2021,1,1)
RETURN
    CALCULATE (
        LASTNONBLANKVALUE ( 'datatable'[Created Date], SUM ( 'datatable'[Value] ) ),
        FILTER ( ALLEXCEPT ( 'datatable', 'datatable'[Item_Num] ), 
            'datatable'[Created Date] <= currentdate 
            && currentdate = Day0)
    )

This actually appears to get me the value that I want for Jan 1.!!  I can do the same code for Feb 1. 

I don't know how to count the number of items that started at a 0 on Jan. 1 and stayed a 0 on Feb. 1.  In the sample, it would be = 1.  

I was trying to figure out how to count with a measure.  I found a few references, but haven't been successful yet.  I think this reference may be key, but am still trying to apply 

https://community.powerbi.com/t5/Desktop/how-to-count-values-of-a-measure/m-p/620262

I would like to be able to get a count that I could put into a card, or even an output that was something like this.  There are only a finite number of these Types below 9 or 12, I think.  So, if I have to make a measure for each, that's fine.

Type           count

0's-->0's |    1

0's-->1's |    1

0's-->2's |    0

etc...

Thanks,
Mike