Forum Discussion

MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago

filtering dates in a calculation

I use this calc to filter a count of a categor (Shipment ID) using a date field to filter (Dest ETA).  This shows me the data required based on last month (monthtToday()=November) so therefore October data. I want to copy/paste this calc to show me only data going back 2 months ie for September ... but cannot seem to get it right. I thought I would just change the -1 to -2?

Any thoughts would be appreciated.

tx

Marc

 
Calc =
CALCULATE (
COUNT ( Input[Shipment ID] ),
FILTER (
Input,
Input[Dest ETA]
>= DATE ( YEAR ( today() ), MONTH ( TODAY() ) - 1, 1 )
&& Input[Dest ETA] < DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), 1 )
)
)
Need Help

6 Replies

    • MarcUrdang's avatar
      MarcUrdang
      Post Patron
      6 years ago

      tx but this doesn't seem to work .. the today() is underllined in red and the error says:  the fist argument to DATEADD must specify a column

       

      any thoughts?

      • az38's avatar
        az38
        Community Champion
        6 years ago

        hi MarcUrdang 

        it looks like your initial statement was correct but to filter only september you should not only to change -1 on -2 but try to write "Input[Dest ETA] < DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), 1 )" as

        Input[Dest ETA] < DATE ( YEAR ( TODAY() ), MONTH ( TODAY()) -1, 1 )

         

    • MarcUrdang's avatar
      MarcUrdang
      Post Patron
      6 years ago

      Also perhaps I wasn't clear .. if you look at my first calc  ... you will see I count the measure in the calc along with having built in the filter as I need to use this answer in another calculation. 

       

      Your previous solution wouldn't help in this regard.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi MarcUrdang 

    I create three measures,count of only month 10, count of month 9 and 10, count of only month 9.

    Which do you want?

    only 10 =
CALCULATE (
    COUNT ( Sheet6[id] ),
    FILTER (
        Sheet6,
        Sheet6[date]
            >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
            && Sheet6[date]
                < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) , 1 )
    )
)

last two months(9,10) =
CALCULATE (
    COUNT ( Sheet6[id] ),
    FILTER (
        Sheet6,
        Sheet6[date]
            >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 2, 1 )
            && Sheet6[date]
                < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
    )
)


only 9 =
CALCULATE (
    COUNT ( Sheet6[id] ),
    FILTER (
        Sheet6,
        Sheet6[date]
            >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 2, 1 )
            && Sheet6[date]
                < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
    )
)

    If you still have probelms, please show me more details:

    eg, the slicers applied on this visual, 

          what columns added on the visual,

          what's the final result you like.

     

    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.