Adding "Show Items in the last 1 Calendar Months" to a Measure

[jjattwood]

Hi All,

I have an issue I can't solve for a monthly report.

I have a card that displays design fee won, design fee tendered, and win % revenue:

This win % revenue should display the percentage difference between the 2 i.e 5%

Its not showing this because both the design fee tendered and won have a filter on them that filters table values. Design Fee tendered is filtered by 'Tender Submission Date (when the tender was submitted to a client)' and design fee won by 'date won' (only applies to jobs in the active or closed category)

The win % revenue is also filtered by tender submission date.

What this means is that its calculating the win % revenue of tenders that were submitted during this set time (last 1 calendar month) but it's not taking into account the jobs that were won last calendar month but may have been submitted 6 months before.

The only was I can think of fixing this is by adding the filters to the measures directly, rather than filters, and then creating a card that does this division itself i.e 68,107/1,317,386.

Can't understand how to add this in as I'm new to powerBI.

I want to create a measure that calculates both the 'design fee tendered' as last 1 calendar month of 'tender submission date' and a measure for 'design fee won' in the last 1 calendar month of 'date won'

Many thanks!

[jjattwood]

bump

[lbendlin]

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good).

[jjattwood]

Hi Ibendlin

Please see below

Job No	Job Stage (Select Dropdown)	Date of Job Stage Change (e.g. In Preparation to Submitted)	Date of RFP/Opp Receipt	Sector (Select Dropdown)	Design Fee Submitted (Local Currency)	Tender Submission Date (DD/MM/YYYY)	Date Won
C463V1	Active	30/05/2021	30/04/2021	Marine and Coastal	2,500	10/05/2021	30/05/2021
C521B	Active	01/09/2021	02/05/2021	Environment	-	09/05/2021	01/09/2021
C557	Closed	01/02/2021	24/01/2021	Environment	600	31/01/2021	25/03/21
C575B	Active	06/09/2021	04/08/2021	Marine and Coastal	2,900	16/08/2021	06/09/2021
C582	Active	18/03/2021	25/04/2020	Marine and Coastal	59,175		18/03/2021
C615V1	Active	23/02/2021	24/01/2021	Marine and Coastal	96,313	03/02/2021	23/02/2021
C615V2	Active	15/07/2021	15/06/2021	Marine and Coastal	27,123	25/06/2021	15/07/2021
C615V3	Active	30/08/2021	31/07/2021	Marine and Coastal	14,810	10/08/2021	30/08/2021
C656	Active	16/09/2021	09/09/2020	Marine and Coastal	18,332	16/09/2020	16/09/2021

[lbendlin]

thank you for the sample data. It seems to be missing the "Design Fee Won"  column?

[jjattwood]

Hi Ibendlin,

Appreciate your help with this.

Design Fee Won is a measure I have on the dashboard. It is the total of design fee submitted for Active and Closed jobs with the Date Won as a filter. 

In this example it shows only Active and Closed, but there are other stages such as Lost, Cancelled, Did Not Bid or Submitted.

Design Fee Tendered is a measure of the 'Design Fee Submitted' for values in the Active, Closed, Lost & Submitted stages filtered by the 'Tender Submission Date' column.

Hope this helps?