How to find the first date on a value

[Glaglagla]

Hello,

First of all, I'm sorry in advance if I'm not clear enough. I tried to post my problem on a french forum but nobody answers, so I'm trying out there but my english isn't perfect at all. Thanks for you patience.

Working on Power Pivot. XL 2013.
So, I'm working on a file wich contains Program ID, broadcast dates, platforms delivery, channel, and definition. Program ID are all duplicated because a program is broadcasted on several platforms (PC, TV, etc.), on several channels (VOD, etc.), and several definition (SD, HD).
I used a DISTINCTCOUNT to count the programs that we worked on, and it works perfectly if I do not include months in my table; in fact, if a programm is broadcasted first in january in SD, and then broadcasted in february in HD, it will be taken into the accounts of both month, BUT I only want to see the first broadcast date (I want to count, monthly, the number Of new program that are broadcasted).

So I would like to "tag" the first broadcast date by using a power pivot formula, creating a calculated column "first broadcast" with yes/no and then, change my DISTINCTCOUNT by CALCULATE(DISTINCTCOUNT(IDProgram);FirstBroadcast="yes").

And I cannot find the formula to tag the first broadcast date.

Am I clear enough?

Thank you for your help!
Gladys

 

[Kazlik]

Something like this might work for you.

=IF(CALCULATE(FIRSTDATE(Sheet1[broadcast dates]),FILTER(Sheet1,Sheet1[Program ID]=EARLIER(Sheet1[Program ID]))) = Sheet1[broadcast dates], "Yes", "No")

 

[Glaglagla]

I think you just saved my life. thank you very much. It seems to be working. I need to go a litlle further into the analysis to confirm but fo now it's perfect.

 

[Glaglagla]

Hello again,
I would like to understand the formula to re use it in other conditions but I don't really get everything.
I understand that:
1- FIRSTDATE find the first broadcast date
2- FILTER reduce the research of the first date to the same program ID
3- earlier?: I don't really understand how it works, and how it combines with FILTER.


As an example, I think I could use something like that to search for another thing: I have another Table with a lot of material related to programs. So one program can have multiple brodcast materials. Each material has a "Type" and a creation date. I would like to find the last material created for each Type.
So it would say: Find, for this Program ID, all material that are "Master" (Type), and give me the Material ID that has last been created.
Column name would be like: program ID, Material ID, creation Date, Type.

Maybe if you give me the formula I can understand better the way it works. But I would really like to understand what I do

Would you like to help, please?

Thanks a lot.

 

[Glaglagla]

I'm not sure that I'm understandable with my bad english: I don't get whay you apply the Program ID to the EARLIEST function instead of applying Program ID + Brodcast date. How does it know that it is the first? And what if I want to get the fisrt broadcast date of 2015 year, as an example?
You know what I mean?
Thanks again for your help!

 

[scottsen]

They key part is this:

=CALCULATE (
        FIRSTDATE ( Sheet1[broadcast dates] ),
        FILTER (
            Sheet1,
            Sheet1[Program ID] = EARLIER ( Sheet1[Program ID] )
        )
    )

EARLIER() is always a bit confusing, but what this portion of DAX is doing is saying (for every row in your table, one at a time...) "Hey, give me the FIRSTDATE() for all rows that match my Program ID" As you are evaluatinng the "one row at a time" -- the EARLIER function is keeping track of that one row. But the FILTER() function is against the whole table... until you filter it down to ... just certain Program ID (the ones that match the EARLIER(), which was the one row at a time).

 

[Kazlik]

Thanks for the explanation Scott, better than what I was going say.

 

[Glaglagla]

Thank you very much guys.

 

[Glaglagla]

Hello again!
This formula worked perfecty until now, but I have an extra data to analyse in my table, and I can't find the way to filter it. I have another column with "yes" and "no" data, and I would like this formula to be calculated only on "yes" rows. I don't see where to add the filter.
To be clear, I would like the EARLIER function to look only on "yes" rows, and to totally forget the "no" rows.

Thanks!

 

[cmcreynolds]

Hello -

Whenever you use the CALCULATE function, you can add on any number of filters separated by columns.

=CALCULATE (
FIRSTDATE ( Sheet1[broadcast dates] ),
FILTER (
Sheet1,
Sheet1[Program ID] = EARLIER ( Sheet1[Program ID] )
), [YOURFIELD] = "Yes"
)

Try that, or if someone else sees this, correct me if I've misunderstood CALCULATE

Good luck!