Forum Discussion

jasonwq's avatar
jasonwq
Icon for Helper I rankHelper I
5 years ago
Solved

Creating a new column filtered by date

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.

 

ITEMENTRY_TYPEQUANTITYDATE
ASALE1008/1/2021

A

SALE1007/1/2021

A

SALE1001/1/2015

B

SALE2001/1/2015

B

SALE2001/1/2010

 

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

 

ITEMRECENT SALES (Last 6 months)
A200
B0

 

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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

5 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      jasonwq
      Icon for Helper I rankHelper I
      5 years ago

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

       

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        5 years ago

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