How to find first day and last day

[florian_bi]

Hello,

I am looking for a DAX function that can reveal a first day and the last day of each campaign, which are stored in one big table.

For each campaign for each month, the start date and the end date is stored in a sep. column. That is in D and C. Now I am wondering if there is any way to search those columns for the "minimum / maximum enrty" within the limitations (or filter) of a name which is given in column A (which will of course be a unique ID in real).

thank you for your help! I am really lost...

[parry2k]

@florian_bi good luck!

[parry2k]

@florian_bi oh! How many times I have seen that, here is the fix.

You should watch these videos on my YT channel if you really want to work with dates.

Add Date Dimension
Importance of Date Dimension
Time Intelligence Playlist

first daye = CALCULATE(MIN(data[ServiceDate]),ALLEXCEPT(data,data[QuoteId]))

[florian_bi]

oh nice. it seems so work know, but still one question: why do i see all month of the year now, when I have the month column in the table?

but it does work indeed, if I just have the ID and the first-date column in the table:

and thank you for the videos! I will watch tomorrow.

[parry2k]

@florian_bi is it possible to share pbix file using one drive/google drive, remove sensitive information before sharing. 

[florian_bi]

yes of course. I reduced it to the id of the campaigns and the start date.  you can download the pbix from this link:

https://www.dropbox.com/scl/fi/5kiu3ikllqtjr31rap9bs/lookingforfirstdate.pbix?rlkey=iy894sbv867kzj2f...

does this file automaticly include the data from the (local) csv or do i have to attach that as well?

[parry2k]

@florian_bi can you share the measure?

[florian_bi]

yes of course. what would I have to do?

this is the formula I setup:

first day = CALCULATE(MIN(Angebotsbelegposten[ServiceDate].[Date]),ALLEXCEPT(Angebotsbelegposten,Angebotsbelegposten[QuoteId]))

[parry2k]

@florian_bi add two measures:

First Day = 
CALCULATE ( MIN ( Table[Start Date] ), ALLEXCEPT ( Table, Table[Campaign] ) )

Last Day = 
CALCULATE ( MAX ( Table[End Date] ), ALLEXCEPT ( Table, Table[Campaign] ) )

[florian_bi]

Hey Parry,

That is what I thought, too. But it does something totally different to the table. Have a look:

The measure I used is this here:

first day = CALCULATE(MIN(Angebotsbelegposten[ServiceDate].[Date]),ALLEXCEPT(Angebotsbelegposten,Angebotsbelegposten[QuoteId]))

Any Idea?