Creating a new column filtered by date

[jasonwq]

I'm having an issue where I want to retrieve certain sales information by date, but I don't want to filter the entire visual by that date.

ITEM	ENTRY_TYPE	QUANTITY	DATE
A	SALE	100	8/1/2021
A	SALE	100	7/1/2021
A	SALE	100	1/1/2015
B	SALE	200	1/1/2015
B	SALE	200	1/1/2010

In the above scenario, I want to to end up with the following report:

ITEM	RECENT SALES (Last 6 months)
A	200
B	0

I have gotten it to work half-way using filters (Date Filter>Relative> 6 months), but then item B does not show up on the report because it does not have any rows that meet the criteria.

Thanks for your help!

[Samarth_18]

Hi @jasonwq ,

You can add one if condition at last like below:-

return if(result = 0,0,result)

It will help you get items which are not having sales in 6 months but they are present.

Thanks,

Samarth

[jasonwq]

I don't understand. Where do I put that code?

[Samarth_18]

Could you please share your code which you have written, I will add this code into it accordingly

[jasonwq]

Sorry, I don't have any code written. I've been using the Filters in PowerBI but this is not working well for me.

[v-yiruan-msft · Accepted solution]

Hi @jasonwq ，

You can create a measure as below:

RECENT SALES (Last 6 months) = 
CALCULATE (
    SUMX (
        'Table',
        IF (
            DATEDIFF ( 'Table'[DATE], TODAY (), MONTH ) < 6
                && DATEDIFF ( 'Table'[DATE], TODAY (), MONTH ) >= 0,
            'Table'[QUANTITY],
            0
        )
    )
)

Best Regards