Last date in fact table in a date interval

[medridha]

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

[v-cazheng-msft]

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!

[medridha]

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 ...

[amitchandak]

@medridha , You can have a measure like

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

or

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