filtering dates in a calculation

[MarcUrdang]

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 )

)

)

[v-juanli-msft]

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.

[az38]

Hi @MarcUrdang 

try

dateadd(today(),-1,month)

[MarcUrdang]

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.

[MarcUrdang]

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]

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]

nope still no luck .. or perhaps I'm not doing it correcty. Would it be ok for me to email you the workbook to try?