DATESINPERIOD filter not working

[ChristineB]

Hi
I am trying to calculate the sum of my optimal load qty (VAR a) for the previous 90days. I have the following formua but it does not seem to be filtering correctly for the date range:

PAR MAX Dispatch Qty P90D =

VAR a =

    CALCULATE (

        MINX ( TOPN ( 1, fact_PAR, [PAR Net Margin/Flight], DESC ), fact_PAR[Value] )

    )

RETURN

    SUMX (

        SUMMARIZE (

            'dim_perishable sell through',

            'dim_perishable sell through'[Flight Key],

            'dim_perishable sell through'[Item Name],

            "PAR MAX value P90D",

                CALCULATE (

                    a,

                    DATESINPERIOD ( dim_date[Date], LASTDATE ( dim_date[Date] ), -91, DAY )

                )

        ),

        [PAR MAX value P90D]

    )

Can anyone please help?

[Anonymous]

Hi @ChristineB ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[ChristineB]

Thank you but it is still not filtering on date range. It is calculating for just the selected date,

[Jihwan_Kim]

please share your sample pbix file, and then I can try to look into it.

thanks.

[ChristineB]

Unfortunately I don't have a sample pbix file to share. Is there any other information I could provide to assist?

I have a date table with a column dim_date[Date] with a 1:many relationship with a date column in my dim_perishable sell through[dep date].

There are no relationships with the fact_PAR table. 

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but try something like below whether it suits your requirement.

PAR MAX Dispatch Qty P90D =
SUMX (
    SUMMARIZE (
        'dim_perishable sell through',
        'dim_perishable sell through'[Flight Key],
        'dim_perishable sell through'[Item Name],
        "PAR MAX value P90D",
            CALCULATE (
                MINX ( TOPN ( 1, fact_PAR, [PAR Net Margin/Flight], DESC ), fact_PAR[Value] ),
                DATESINPERIOD ( dim_date[Date], LASTDATE ( dim_date[Date] ), -91, DAY )
            )
    ),
    [PAR MAX value P90D]
)