show item with no data and exclude Date filter

[Anonymous]

Hi all

I have two table Target and sales

Product	Target
A	50
B	25
C	30
D	10

Sales Date	Product	Sales
2/1/2018	A	2
2/2/2018	B	4
2/3/2018	A	1
2/4/2018	B	5
2/5/2018	A	7
2/6/2018	B	5
2/7/2018	A	2
2/8/2018	B	4
2/9/2018	A	1
2/10/2018	B	1
2/11/2018	A	3
2/12/2018	B	2
2/13/2018	A	3
2/14/2018	B	2
2/15/2018	A	3
2/16/2018	B	1
2/17/2018	A	2
2/18/2018	B	1

I want to show a table like below image 

1. If I select "sales date" from 15 to 18 then it wants to show A=5, B=2, C=0, D=0 in "Sales Count Based on Date Selection field"
2. at the same time, it wants to show A=24, B=25, C=0, D=0 in "Overall sales count field"

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a new table

Date Table = CALENDARAUTO()

don't connect this table to any other table.

create measures in "Target" table

overall sales =
VAR overall =
    CALCULATE (
        SUM ( sales[Sales] ),
        FILTER ( ALL ( sales ), sales[Product ] = MAX ( sales[Product ] ) )
    )
RETURN
    IF ( overall <> BLANK (), overall, 0 )




based on date =
VAR count1 =
    CALCULATE (
        SUM ( sales[Sales] ),
        FILTER (
            ALL ( sales ),
            sales[Product ] = MAX ( sales[Product ] )
                && sales[Sales Date] >= MIN ( 'Date Table'[Date] )
                && sales[Sales Date] <= MAX ( 'Date Table'[Date] )
        )
    )
RETURN
    IF ( count1 <> BLANK (), count1, 0 )

Best Regards

Maggie

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