cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LauraCawdron
New Member

Calculate deadlines from two column of dates

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!                                         

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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)))

8.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

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)))

8.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

LauraCawdronDav
Frequent Visitor

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?

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!
 
ryan_mayu
Super User
Super User

@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

1.PNG

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))

2.PNG

plesae see the attachment below

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




EBrusse
Frequent Visitor

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!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!