topic Re: How to Dynamic date period in DAX Commands and Tips

How to Dynamic date period

tkconsultingnl — Thu, 23 Jun 2022 08:11:06 GMT

Hi PBI Expert,

I am working with different mesaures, I would like to have year, month, dynamic mesaures created. I have a table with various data, but I don't want to be selecting runperiod manually I would like to automate the year and month to show the following in the table:

1. Current Period

2. Previous Month.

3. Previous quater

4. Previous year.

How can I combine all 4 togther in DAX, that when data is been uploaded when I refreshed the repor it should populate the data.

Thanks in davance

Re: How to Dynamic date period

v-jianboli-msft — Mon, 27 Jun 2022 06:55:09 GMT

Hi tkconsultingnl ,

May I ask what range of dates the Current Period specifically refers to? I use it as the current month to do the following steps: 1. Create a new table with all the options you need in the slicer.

2. Turn the single select of the slicer on

3. Then create a measure that filters the date period.

Measure = SWITCH ( MAX ( 'For Slicer'[Value] ), "Current Period", IF ( DATEDIFF ( MAX ( 'Table'[Date] ), TODAY (), MONTH ) = 0, 1, 0 ), "Previous Month", IF ( DATEDIFF ( MAX ( 'Table'[Date] ), TODAY (), MONTH ) = 1, 1, 0 ), "Previous Quarter", VAR currYearQua = YEAR ( TODAY () ) * 100 + QUARTER ( TODAY () ) VAR yearQua = YEAR ( MAX ( 'Table'[Date] ) ) * 100 + QUARTER ( MAX ( 'Table'[Date] ) ) RETURN IF ( currYearQua - yearQua = 1 || currYearQua - yearQua = 97, 1, 0 ), "Previous Year", IF ( YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () ) - 1, 1, 0 )

4. Apply the measure to the visual - level filter , set "is 1":

Final output:

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Jianbo Li

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

Re: How to Dynamic date period

tkconsultingnl — Thu, 14 Jul 2022 09:59:42 GMT

Hi v-jianboili,

Thanks for your heads up on this.

The current period refers to the actual month the data is availabe, so if May data was loaded and June data not available yet it should still show for the May.