Calculate deadlines from two column of dates

[LauraCawdron]

Hi!

I'm trying to calculate the number of 'deadlines' my team has in any given week. These deadlines are currently across two columns ('pitch date' and 'proposal deadline') - dummy data shown below. 

Some dummy data:

                    Proposal deadline       Pitch date

Deal A          01/04/21                     14/04/21

Deal B          20/02/21    

Deal C          14/04/21                      23/04/21

Deal D                                              02/04/21

For this data the card would show two deadlines this week (proposal deadline Deal A, and pitch date Deal D). So I think I need a new column/table, 'Deadlines', which pulls in all dates found in the 'pitch date' and 'proposal deadline' columns, and then a measure which calculates the count of deadlines in this week.  Any help would be greatly appreciated!                                         

[v-jayw-msft · Accepted solution]

Hi @LauraCawdron ,

Check the measure.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Deal]),FILTER(ALL('Table'),WEEKNUM('Table'[Proposal deadline],2)=WEEKNUM(TODAY(),2)||WEEKNUM('Table'[Pitch date],2)=WEEKNUM(TODAY(),2)))

Best Regards,

Jay

[LauraCawdronDav]

Hi @EBrusse and @ryan_mayu - sorry I had to start a new aacount so couldn't respond directly but I am the orginal question asker! 

@ryan_mayuwhen I tried to unpivot is seemed to mess up all of my other columns and visualisations so I went back and took out the unpivot!

@EBrusseUsing your measure seemed to work other than the 'this week' section. In your message you said that as long as the pitch date and proposal deadline are in the same table it should pull through, however, I didn't see any reference to 'This week' in your solution, and when I use either a 'pitch deadline this week' or 'proposal deadline this week' filter, it either picks one or the other and not both. Please can you help me?

[EBrusse]

Hello,

I have solved your problem. You need a date table which is not connected to the table where your deals and dates are stored.

The measure you need is:

CountDeadlines =

VAR ProposalDeadlines =

    CALCULATE(

        COUNT( tblDeals[Proposal deadline] ) ,

        TREATAS( VALUES( tblDates[Date] ) , tblDeals[Proposal deadline] )

    )

VAR PitchDates =

    CALCULATE(

        COUNT( tblDeals[Pitch date] ) ,

        TREATAS( VALUES( tblDates[Date] ) , tblDeals[Pitch date] )

    )

RETURN

ProposalDeadlines + PitchDates

Place the date from the date table in the rows of a MATRIX (so you can use the date hierarchy to get your weeks or put in the weeks if you have that in your date table) and place this measure in the values.

I have recreated your data and have the solution in the attached .pbi file!

https://www.dropbox.com/s/9g8q4lw31wzspgk/DateCount.pbix?dl=0 

[ryan_mayu]

@LauraCawdron 

what if both proposal deadline and pitch date are in this week? I guess you want to count twice.

1. in the pq, select the first column and unpivot other columns

2. create a measure to count deadline, since I am not sure about your real request, I also create a measure to count deal no.

countdeadline = 
VAR _WEEKDAY=WEEKDAY(today(),1)
VAR _START=TODAY()-_WEEKDAY+1
VAR _END=today()+(7-_WEEKDAY)
RETURN CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Dealline]>=_START&&'Table'[Dealline]<=_END))

countdeal = 
VAR _WEEKDAY=WEEKDAY(today(),1)
VAR _START=TODAY()-_WEEKDAY+1
VAR _END=today()+(7-_WEEKDAY)
RETURN CALCULATE(DISTINCTCOUNT('Table'[Deal]),FILTER('Table','Table'[Dealline]>=_START&&'Table'[Dealline]<=_END))

plesae see the attachment below

[EBrusse]

Hello!

If you would simply like to calculate the two seperate dates, you can create the following measure:

MEASURE = 

VAR ProposalDeadlines = COUNT( table[Proposal deadline] )
VAR PitchDates = COUNT( table[Pitch date] )

RETURN
ProposalDeadlines + PitchDates

In your message you say that you want to see the amount of deadlines by week. If these dates are in the same table, the calculation works as-is. If the dates reside from seperate tables, make sure you use a date table and relate those to the different tables!

Let me know if this has helped you!