topic Re: Last date in fact table in a date interval in DAX Commands and Tips

Last date in fact table in a date interval

medridha — Wed, 24 Nov 2021 10:45:25 GMT

Hello All,

I have :

Fact_Sales table (Fact table) with Fk_Dim_Date as date key

Dim_Product table

Dim_Date

Dim_Period

I used a DAX query with interval date and Product_key as input to have the last Sales fact of every month in the Date Interval

Exp:

Interval date: "27/09/2021" - "05/10/2021"

Productkey: "P001"

What we have as records:

What we want: last record in fact in every Month, so we want the two records in red

I have the last date for every month but i havent the right result for Capital, Net ...

Thanks in advance

Re: Last date in fact table in a date interval

amitchandak — Wed, 24 Nov 2021 10:49:17 GMT

medridha , You can have a measure like

calculate(lastnonblankvalue('Date'[Date],[Captial N]), allexcept(Table, Table[Product], [Period], [Month Year]))

calculate(lastnonblankvalue('Date'[Date],[Captial N]), allexcept(Table, Table[Product] = max(Table[Product]) && [Period] = max([Period]) && [Month Year] = max( [Month Year]) ))

Re: Last date in fact table in a date interval

medridha — Wed, 24 Nov 2021 11:06:13 GMT

Hi amitchandak

Thanks for your reply, i should have an Evaluate DAX Query (the query result is used in Excel file)

The actual query :

EVALUATE
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            'Dim_Product'[Product_Key],
            'Dim_Period'[Period_Key],
            'Dim_Date'[Date],
            'Dim_Date'[FirstOfMonth],
            FILTER (
                VALUES ( 'Dim_Product'[Product_Key] ),
                ( 'Dim_Product'[Product_Key] = "PR001" )
            ),
            FILTER (
                VALUES ( 'Fact_Sales'[FK_Dim_Date] ),
                Fact_Sales[FK_Dim_Date]
                    >= DATEVALUE ( "27/09/2021" ) + TIMEVALUE ( "27/09/2021" )
                    && Fact_Sales[FK_Dim_Date]
                        <= DATEVALUE ( "05/10/2021" ) + TIMEVALUE ( "05/10/2021" )
            ),
            FILTER (
                VALUES ( 'Dim_Date'[Date] ),
                'Dim_Date'[Date]
                    >= DATEVALUE ( "27/09/2021" ) + TIMEVALUE ( "27/09/2021" )
                    && 'Dim_Date'[Date]
                        <= DATEVALUE ( "05/10/2021" ) + TIMEVALUE ( "05/10/2021" )
                    && 'Dim_Date'[Date] = LASTDATE ( Fact_Sales[FK_Dim_Date] )
            ),
            "NetFlat", CALCULATE ( [NetFlat], FILTER ( Dim_Period, Dim_Period[Period_Key] = "P1" ) ),
            "CumulReturn",
                CALCULATE (
                    TOTALYTD ( [NetFlat], Dim_Date[Date] ),
                    FILTER ( Dim_Period, Dim_Period[Period_Key] = "P1" )
                ),
            "CAP_1",
                CALCULATE (
                    [CAP],
                    PARALLELPERIOD ( Dim_Date[Date], -1, MONTH ),
                    FILTER ( Dim_Period, Dim_Period[Period_Key] = "P1" )
                ),
            "CAP", CALCULATE ( [CAP], FILTER ( Dim_Period, Dim_Period[Period_Key] = "P1" ) ),
            "MVP", [MVP]
        ),
        "Product", [Product_Key],
        "Period", [Period_Key],
        "FirstDate", Dim_Date[FirstOfMonth],
        "LastDate", 'Dim_Date'[Date], //"LastDate", LASTDATE ( Fact_Sales[FK_Dim_Date] ),
        "Capital N-1", [CAP_1],
        "Capital N", [CAP],
        "Net", [NetFlat]
        "MVP" , [MVP]
    )

When i replace 'Dim_Date'[Date] by 'Dim_Date'[MonthName] i have the right date (only two rows but i havent the right value for measure like CAP, Net ...

Re: Last date in fact table in a date interval

v-cazheng-msft — Mon, 29 Nov 2021 07:16:23 GMT

Hi medridha

Is your issue resolved? If you still have problems on it? Could you please provide some sample data related to this dax formula and let me how you create relationships for these tables?

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!