How to find the first date on a value

Glaglagla

New Member
Joined
Jun 2, 2015
Messages
9
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Kazlik

Board Regular
Joined
Dec 9, 2014
Messages
68
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

New Member
Joined
Jun 2, 2015
Messages
9
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

New Member
Joined
Jun 2, 2015
Messages
9
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

New Member
Joined
Jun 2, 2015
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
They key part is this:
Code:
=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

Board Regular
Joined
Dec 9, 2014
Messages
68

ADVERTISEMENT

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

Glaglagla

New Member
Joined
Jun 2, 2015
Messages
9
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

Active Member
Joined
May 21, 2015
Messages
295
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,562
Messages
5,625,525
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top