DAX to identify time period selection for ALL Markets where they have common date (Month-Year) data

[Manisha_91]

Hi Team,

I am new to Power BI and trying to create a DAX to identify time period selection for ALL Markets where they have common date (Month-Year) data available.

For example: The Sales data avaliable for all the markets are as below:

Market	Latest Data Available Date Range
USA	Oct 2021 - May 2022
Spain	Oct 2021 - July 2022
UK	Oct 2021 - June 2022
Italy	Oct 2021 - May 2022
Germany	Oct 2021 - May 2022
France	Oct 2021 - July 2022
Russia	Oct 2021 - May 2022

Requirement is to showcase the market sales for common Max date data available.
For example The report should showcase Sales data from Oct 2021 till May 2022 for All Markets.

Can anyone help me with the dax formula for this?

[tamerj1]

Hi @Manisha_91 
Sorry for the late reply, I was about to leave the office when I received your feedback. I hope I correctly understand. You want to filter your report for the dates that are less than or equal to maximum common date of all markets. Here is is a sample file with the solution https://www.dropbox.com/t/tqGbPqbyVVSPFLXH

Most probably your data model is a bit different. However, if it does not work with your actual data model then minor amendments to the code shall be enough to get it work.

You need to create a filter measure, place it in the filter pane, select "Is not blank" and apply the filter.

Please let me know if you have any question.

Filter Measure = 
VAR CurrentDate = 
    SELECTEDVALUE ( Sales[Date] )
VAR T1 =
    CALCULATETABLE ( SUMMARIZE ( Sales, Sales[Market], Sales[Date] ), ALL ( Sales[Market], Sales[Date] ) )
VAR T2 = 
    ADDCOLUMNS ( T1, "@MaxDate", CALCULATE ( MAX ( Sales[Date] ), ALL ( Sales[Date] ) ) )
VAR T3 =
    FILTER ( T2, [Date] = [@MaxDate] )
VAR LastCommonDate =
    MINX ( T3, [Date] )
VAR Result =
    IF ( CurrentDate <= LastCommonDate, 1 )
RETURN
    Result

[Manisha_91]

Hi @tamerj1 ,

I tried this solution with my data. It is returning 1 for all the months even the one that are not common amognst the Markets. Please refer the screenshot attached.

Also, I am not able to see the attachment you had in dropbox. It is giving error.
Could you please try uploading it again?

[tamerj1]

@Manisha_91 

I'll find the and share it with you. Mean while would you please elaborate on you data model

[Manisha_91]

@tamerj1 

I have one fact table (Fact Retail Sales) and 2 dimemsion Tables dimTime and dimProduct

Fact table structure:

dimTime Structure:

Join is one to Many and primary key is 'Date' column

Requirement is - 
We are working on 3 months, 6 months latest period selection which are common between all the markets which in this screnario should be 
3 Month -> Mar 2022 to May 2022
6 Month -> Dec 2021 to May 2022

If I am selecting latest three month slicer it shows data as below screenshot:

As we see Spain and Australia have June Month data as well in fact table but other markets do not have it so we want to showcase data till May 2022 for All Markets.

[tamerj1 · Accepted solution]

Hi @Manisha_91 
Sorry for the late reply. It turned out I have a problem with my dropbox. Anyway, here is the sample file with the updated solution based on date table https://we.tl/t-ptCNoiaUEL

Filter Measure = 
VAR CurrentDate = 
    CALCULATE ( SELECTEDVALUE ( DimDate[Date] ), CROSSFILTER ( DimDate[Date], Sales[Date], BOTH ) )
VAR T1 =
    CALCULATETABLE ( SUMMARIZE ( Sales, Sales[Market], Sales[Date] ), ALL ( DimDate ), ALL ( Sales[Market] ) )
VAR T2 = 
    ADDCOLUMNS ( 
        T1, 
        "@MaxDate", CALCULATE ( MAX ( Sales[Date] ), ALLEXCEPT ( Sales, Sales[Market] ) ),
        "@MinDate", CALCULATE ( MIN ( Sales[Date] ), ALLEXCEPT ( Sales, Sales[Market] ) ) 
    )
VAR T3 =
    FILTER ( T2, [Date] = [@MaxDate] )
VAR T4 =
    FILTER ( T2, [Date] = [@MinDate] )
VAR LastCommonDate =
    MINX ( T3, [Date] )
VAR FirstCommonDate =
    MAXX ( T4, [Date] )
VAR Result =
    IF ( CurrentDate >= FirstCommonDate && CurrentDate <= LastCommonDate, 1 )
RETURN
    Result

[Manisha_91]

Thanks @tamerj1 This really helps. 🙂

[tamerj1]

Hi @Manisha_91 
Seems something missing here. Where are the values of the latest data available?

[Manisha_91]

..

[tamerj1]

@Manisha_91 
Tahnks for clarifying, please advise what would be the expected results for this sample data?

[Manisha_91]

@tamerj1 , I am expecting that if all Markets have May 2022 data then the report should only reflect data till May 2022.

Basically a Max cutoff for All Markets should be same.
As in Sample data we see Spain, UK and France have data more than May 2022 but if we can build a measure we can provide a cutoff till May 2022. I hope I am making sense here.

[tamerj1]

@Manisha_91 
Thank you. Please consider that I'm not looking at your data neither do I have any idea what is it. So please bare with me. So you row data is preseneted exactly as you stated in the sample above? And yes we can limit the any calculation up the latest common date (May 2022) but what calculation? What exactly are you trying to limit? 

[Manisha_91]

@tamerj1 

My Report has different Sales related insights.

We want to limit the sales data to reflect insights till a date that is common in All the Markets. 

Please refer the table below as I have added Sales amount column as well per month:

Market	Date	Sales
USA	Oct-21	$    25,000
USA	Nov-21	$    23,156
USA	Dec-21	$    20,000
USA	Jan-22	$    14,789
USA	Feb-22	$    45,987
USA	Mar-22	$    23,452
USA	Apr-22	$    32,141
USA	May-22	$    11,212
Spain	Oct-21	$    45,987
Spain	Nov-21	$    23,452
Spain	Dec-21	$    32,141
Spain	Jan-22	$    65,467
Spain	Feb-22	$    32,211
Spain	Mar-22	$    54,543
Spain	Apr-22	$  345,265
Spain	May-22	$    65,311
Spain	Jun-22	$    90,123
Spain	Jul-22	$    54,376
UK	Oct-21	$    32,141
UK	Nov-21	$    65,467
UK	Dec-21	$    32,211
UK	Jan-22	$    14,789
UK	Feb-22	$    45,987
UK	Mar-22	$    23,452
UK	Apr-22	$    32,141
UK	May-22	$    50,000
UK	Jun-22	$    65,345
Italy	Oct-21	$    45,987
Italy	Nov-21	$    23,452
Italy	Dec-21	$    32,141
Italy	Jan-22	$    65,467
Italy	Feb-22	$    32,211
Italy	Mar-22	$    54,543
Italy	Apr-22	$    14,789
Italy	May-22	$    45,987
Germany	Oct-21	$    21,212
Germany	Nov-21	$    35,213
Germany	Dec-21	$    85,453
Germany	Jan-22	$    90,000
Germany	Feb-22	$  152,012
Germany	Mar-22	$    57,481
Germany	Apr-22	$    32,123
Germany	May-22	$    22,514
France	Oct-21	$    23,156
France	Nov-21	$    20,000
France	Dec-21	$    14,789
France	Jan-22	$    45,987
France	Feb-22	$    23,452
France	Mar-22	$    65,467
France	Apr-22	$    32,211
France	May-22	$    14,789
France	Jun-22	$    45,987
Russia	Oct-21	$    23,452
Russia	Nov-21	$    23,452
Russia	Dec-21	$    32,141
Russia	Jan-22	$    65,467
Russia	Feb-22	$    32,211
Russia	Mar-22	$    42,000
Russia	Apr-22	$    32,500
Russia	May-22	$    38,562