Forum Discussion
How to Dynamic date period
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
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.
2 Replies
v-jianboli-msftCommunity Support
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.
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.
