Only show the data of last 6 month (Created date) based on Finalised date

[NickProp28]

Dear Community, 

Hope you are doing well.

I have these raw data, and the 'Finalised date' will be the year and month slicer. 

My request measure is, the table will only display data for the last six months of 'Created date' whenever you click on the year/month slicer (Finalize date).

For example, when user click on month slicer "July". (Finalised date),

Only display the data which 'created date' from January to June. (Last six month)

**Display only S001 and S004 because they fall under the last six months, S005 and S006 will be excluded.

Attached with the pbix: https://drive.google.com/file/d/1V-tZTGIFs2pZv1RgR27KFmfQETL1VvtW/view?usp=sharing

Greatly appreciated any help provided.

Thank you!

[v-yangliu-msft · Accepted solution]

Hi  @NickProp28 ,

Here are the steps you can follow：

1. Create calculated table.

Slicer table：

Slicer = DISTINCT('Customise'[Finalised date])

Date table:

Date =
CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

2. Create measure.

Flag =
var _selectmonth=SELECTEDVALUE('Slicer'[Finalised date].[Month])
var _selectyear=SELECTEDVALUE('Slicer'[Finalised date].[Year])
var _monthnumber=
CALCULATE(MAX('Date'[Month_number]),FILTER(ALL('Date'),'Date'[Month]=_selectmonth))
var _currentmaxdate=EOMONTH(DATE(_selectyear,_monthnumber,1),-1) //2022.6.30
var _mindate=DATE(_selectyear,_monthnumber-6,1) //2022.1.1
return
 IF(
     MAX('Customise'[Created Date]) >=_mindate&&MAX('Customise'[Created Date]) <=_currentmaxdate&&MONTH(MAX('Customise'[Finalised date]))=_monthnumber,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Use the [Finalised date] of the Slicer table as the slicer.

Best Regards,

Liu Yang

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

[rajulshah]

Hello @NickProp28 ,

Please try the following DAX formula. Also, in your result, I think S002 should also be involved.

Total Revenue =
CALCULATE (
    SUM ( Customise[Revenue] ),
    DATESINPERIOD ( Dates[Date], FIRSTDATE ( Dates[Date] ) - 1, -6, MONTH ),
    USERELATIONSHIP ( Customise[Created Date], Dates[Date] )
)

The result that I am getting is as below:

Please let me know if this is not what you want.

[NickProp28]

Dear @rajulshah ,

Thanks for your prompt response. 

Because I usually use the whole month without the date, S002 will not be included

Could you please send me your pbix, since I'm having a problem with the code you provided.

Thank you so much!

[rajulshah]

@NickProp28 ,

Here is the link:
https://drive.google.com/file/d/1EFJNIt8yqg8Ad86vjBO2jrndSsTl_VWX/view?usp=sharing

[NickProp28]

Dear @rajulshah ,

Could you please grant me access to the file you shared with me?

Thank you!