Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Min Date return value based on filter applied

Hi,

I have Created a calculated measure to get the min date for a particular filter e.g. Calculate(min(date), filter(countryname="US"))

This give min date based on the date table and not taking the filter into consideration
E.g. Min date for US is 08092017
This is expected, but am getting only the min date in the date table which is 01011970
Tried excluding the date table in the Expression..and surfed over net no luck

 

Thanks,

Aravind

1 ACCEPTED SOLUTION
Anonymous
Not applicable

As per my reply above, I use date from date table and date_id is alone present in the fact.

I was able to resolve by creating the dim column within the fact and created the metric with the expression worked for the formula

 

Calculate(min(full_date),filter(fact,fact[country_name]))

View solution in original post

7 REPLIES 7
itsmebvk
Continued Contributor
Continued Contributor

@Anonymous  Not sure what you have done. I did the same thing I am getting min date based on filter criteria. Please see below mentioned screenshot.

Measure = 
CALCULATE ( Min ( Sheet1[Date].[Date] ), FILTER(Sheet1,Sheet1[Name]="A" ))

 

Test.PNG 

Anonymous
Not applicable

I see you use one table here, in my case it uses two tables, eg: date and country table. when i tried to filter from same table i get the output correctly

Hi @Anonymous ,

 

If above formula does not work, could you please describle the main fields of each tables and the relations between tables simply? Please don't have any Confidential Information or Real data in your reply.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-lid-msft ,

 

The above formula is not working.

 

I have two dimension tables joining via fact. As mentioned above i was trying to retrieve the minimum date by country.

Dimensions used:

dim_country

dim_date


fact: 

fact_sales

 

Example:

 

Date_IDCountryDateMin DateSum(Sales)
01012018US01/01/201801/01/19703
02012018US02/01/201801/01/19703
03012018US03/01/201801/01/19703
04012018US04/01/201801/01/19703

 

Here I get the correct ID and value, but am facing issue when i was trying to get the Min or Max date by country which is returning the min and max date in the dim_date table. (Above is sample value created to showcase the result am getting)

Hi @Anonymous ,

 

We could try to use the following measure to meet your requirement:

 

MinDateByCountry =
CALCULATE (
    MIN ( 'fact_sales'[Date] ),
    FILTER (
        ALLSELECTED ( 'fact_sales' ),
        'fact_sales'[Country] IN FILTERS ( 'dim_country'[Country] )
    )
)

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

As per my reply above, I use date from date table and date_id is alone present in the fact.

I was able to resolve by creating the dim column within the fact and created the metric with the expression worked for the formula

 

Calculate(min(full_date),filter(fact,fact[country_name]))

itsmebvk
Continued Contributor
Continued Contributor

@Anonymous  did you try something like this?

 

Measure =
CALCULATE ( Min ( Client[Date] ), RELATEDTABLE(Country))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.