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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
NickProp28
Post Partisan
Post Partisan

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

Dear Community, 

 

Hope you are doing well.

NickProp28_0-1655975839590.png

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)

NickProp28_2-1655976775982.png

**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!

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @NickProp28 ,

Here are the steps you can follow:

1. Create calculated table.

Slicer table

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

vyangliumsft_0-1656386363853.png

Date table:

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

vyangliumsft_1-1656386363855.png

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.

vyangliumsft_2-1656386363856.png

4. Result:

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

vyangliumsft_3-1656386363856.png

 

 

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

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @NickProp28 ,

Here are the steps you can follow:

1. Create calculated table.

Slicer table

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

vyangliumsft_0-1656386363853.png

Date table:

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

vyangliumsft_1-1656386363855.png

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.

vyangliumsft_2-1656386363856.png

4. Result:

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

vyangliumsft_3-1656386363856.png

 

 

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
Super User
Super User

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:

rajulshah_0-1655979031823.png

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

Dear @rajulshah ,

 

Thanks for your prompt response. 

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

NickProp28_0-1656010240007.png

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

Thank you so much!

 

Dear @rajulshah ,

 

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

Thank you!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.