multiple date in fact table, need to create the measure with both date

[kavimk]

Hi All,

Order Fact:

Order Create date

Financial close date
Date Hierarchy:
Date

Active relationship: Date Hierarchy[Date] = Order Fact[order create date)

Inactive relationship: Date Hierarchy[Date] = Order Fact[fiancial close date)

I want to create the Measure with filter 
Calculate ( sum( order amount)),Filter(order fact,
order date < 2023-11-01 && financial close date>=2023-11-01))
I want to work this measure dynamically by user selection of month from date hierarchy

Thanks for your support

[v-xinruzhu-msft]

Hi @kavimk 

As @amitchandak  mentioned, you can use crossfilter() function

You can refer to the following sample.

Sample data 

Date table

You can create a measure.

Measure =
IF (
    ISFILTERED ( 'Date'[Date].[Month] ),
    CALCULATE (
        SUM ( 'Order Fact'[order amount] ),
        EOMONTH ( 'Order Fact'[Order Create date], 0 )
            <= EOMONTH ( MAX ( 'Date'[Date] ), 0 )
            && EOMONTH ( 'Order Fact'[Financial close date], 0 )
                >= EOMONTH ( MAX ( 'Date'[Date] ), 0 ),
        CROSSFILTER ( 'Date'[Date], 'Order Fact'[Order Create date], NONE )
    ),
    CALCULATE ( SUM ( 'Order Fact'[order amount] ) )
)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kavimk]

Thanks for your support
order Create < 2023-11-01 which means it will give all period 2022,2021,2020 when finacial close date >=2023-11-01

EOMONTH work on specific month not enitre month

[v-xinruzhu-msft]

Hi @kavimk 

You can try the following measure

Measure =
VAR a =
    EOMONTH ( MAX ( 'Date'[Date] ), -1 ) + 1
//it will return the first day of the month you have chosen
RETURN
    IF (
        ISFILTERED ( 'Date'[Date].[Month] ),
        CALCULATE (
            SUM ( 'Order Fact'[order amount] ),
            'Order Fact'[Order Create date] <= a
                && 'Order Fact'[Financial close date] >= a,
            CROSSFILTER ( 'Date'[Date], 'Order Fact'[Order Create date], NONE )
        ),
        CALCULATE ( SUM ( 'Order Fact'[order amount] ) )
    )

And EOMONTH() means it will return the last day of the month, not the specific month, it wll also return the entire month, EOMONTH(date[date],0) means it will return all the last dates of the months of each date, You can see the following picture.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kavimk]

Thanks for response Expected result is,

Measure=

CALCULATE(SUM('Orders Fact'[Amt]),

FILTER('Date Hierarchy','Date Hierarchy'[Year Month]<"2024-JAN")

,FILTER('Date Hierarchy Fin_cls','Date Hierarchy Fin_cls'[Year Month]=2024-JAN))

This measure should work dynamically, if you notice order create value we have data periods in past

[v-xinruzhu-msft · Accepted solution]

Hi @kavimk 

The measure I offerd change dynamically, you can choose the month, it will return all the date that meet the condition(include the past period),you can download the pbix I have offered at the 5th message.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@kavimk , Have tried using userelationship to create the measure

Calculate ( sum( order amount)), userelationship('Date Hierarchy'[Date] , 'Order Fact'[fiancial close date]))

example

HR Analytics- Hired, terminated, and active employees using UserRelationships and crossfilter: https://youtu.be/K14OPlGc9E8