Forum Discussion

MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago
Solved

Date filtering

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
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi MarcUrdang 

    Yes, you could just change the -1 to -2 for your requirement.

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

2 Replies

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

    hi MarcUrdang 

    Yes, you could just change the -1 to -2 for your requirement.

    Calc =
    CALCULATE (
    COUNT ( Input[Shipment ID] ),
    FILTER (
    Input,
    Input[Dest ETA]
    >= DATE ( YEAR ( today() ), MONTH ( TODAY() ) - 2, 1 )
    && Input[Dest ETA] < DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), 1 )
    )
    )
     
    Regards,
    Lin
  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    MarcUrdang for any date related calculations, it is best practice to have Date dimension in your model. There are many blog posts on how to add Date dimension in your model. Once you have Date table, set relationship between you transaction table and date table and use Date from date table in your measure for these time intelligence calculations.