Multiple date type columns

[KarlNixon]

Help please - I'm new!

I want to write a report that shows for a selected date range (say December) the number of quotations issued and number of quotes won in the month.

For example I have the following data set

Effectively I want to calculate the quoted qty with reference to the [Quote date] and ignore the [Date won or lost] and calculate the Won or Lost quantity by referencing the [Date Won or Lost] but ignore the Quote Date

Any assistance would be greatly appreciated.

[moumipanja · Accepted solution]

Does this layout work?

1.To get this one, go to Edit Queries -> Right click on your Table and duplicate it -> Let's name the tables as Table1 and Table2

2. Delete Date Won or Lost and Status columns from Table 1 

3. Delete Quote Date column in Table 2

4. Click on Close&Apply to return to the Report View

5. Create a Date table and establish the connection with Table1 and Table2

Date = CALENDARAUTO()

6. Create 3 measures to calculate Qty Quoted, Qty Won and Qty Lost as below

Qty Quoted = 
SUMX(
	KEEPFILTERS(VALUES('Table1'[Quote Date])),
	CALCULATE(SUM('Table1'[Qty]))
)

Qty Won = 
CALCULATE(SUM('Table2'[Qty]), Table2[Status]="W", KEEPFILTERS(Table2[Date Won or Lost]))

Qty Lost = 
CALCULATE(SUM('Table2'[Qty]), Table2[Status]="L", KEEPFILTERS(Table2[Date Won or Lost]))

7. Display these measures in a Matrix layout as Values and Date column from newly created Date table as Rows with hierarchy as Year and Month

[v-jiascu-msft · Accepted solution]

Hi @KarlNixon,

Please download another solution from the attachment. 

1. Create a date table. 

Calendar = CALENDARAUTO()

2. Establish two relationships while only one is active.

3. Create three measures.

QTY Quoted = sum(Table1[QTY])

QTY Won =
CALCULATE (
    SUM ( Table1[QTY] ),
    USERELATIONSHIP ( 'Calendar'[Date], Table1[Date Won or Lost] ),
    Table1[Status] = "W"
)

QTY Lost =
CALCULATE (
    SUM ( Table1[QTY] ),
    USERELATIONSHIP ( 'Calendar'[Date], Table1[Date Won or Lost] ),
    Table1[Status] = "L"
)

Best Regards,

[KarlNixon]

Thank you - that is the perfect solution!

Much appreciated!!!!

[moumipanja · Accepted solution]

Does this layout work?

1.To get this one, go to Edit Queries -> Right click on your Table and duplicate it -> Let's name the tables as Table1 and Table2

2. Delete Date Won or Lost and Status columns from Table 1 

3. Delete Quote Date column in Table 2

4. Click on Close&Apply to return to the Report View

5. Create a Date table and establish the connection with Table1 and Table2

Date = CALENDARAUTO()

6. Create 3 measures to calculate Qty Quoted, Qty Won and Qty Lost as below

Qty Quoted = 
SUMX(
	KEEPFILTERS(VALUES('Table1'[Quote Date])),
	CALCULATE(SUM('Table1'[Qty]))
)

Qty Won = 
CALCULATE(SUM('Table2'[Qty]), Table2[Status]="W", KEEPFILTERS(Table2[Date Won or Lost]))

Qty Lost = 
CALCULATE(SUM('Table2'[Qty]), Table2[Status]="L", KEEPFILTERS(Table2[Date Won or Lost]))

7. Display these measures in a Matrix layout as Values and Date column from newly created Date table as Rows with hierarchy as Year and Month