Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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:
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
Proud to be a Super User!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |