Calculation group for dynamic date 08-12-2025 23:41 PM

[davidkofodhanna]

"𝗚𝗶𝘃𝗲 𝗺𝗲 𝗮𝗻 𝗲𝗮𝘀𝘆 𝗼𝗽𝘁𝗶𝗼𝗻 𝘁𝗼 𝘀𝘄𝗶𝘁𝗰𝗵 𝗱𝗮𝘁𝗲 𝗽𝗲𝗿𝗶𝗼𝗱𝘀 & 𝘾𝙪𝙨𝙩𝙤𝙢 𝙙𝙖𝙩𝙚𝙨"

📅 Let's make it userfriendly and with a click of a button - the end user can set predefined date slicers + 𝘴𝘵𝘪𝘭𝘭 𝘨𝘪𝘷𝘦 𝘵𝘩𝘦𝘮 𝘵𝘩𝘦 𝘰𝘱𝘵𝘪𝘰𝘯 𝘰𝘧 𝘢 𝘤𝘶𝘴𝘵𝘰𝘮 𝘥𝘢𝘵𝘦 𝘳𝘢𝘯𝘨𝘦.

All done with 𝗰𝗮𝗹𝗰𝘂𝗹𝗮𝘁𝗶𝗼𝗻 𝗴𝗿𝗼𝘂𝗽𝘀 𝘁𝗼 𝘁𝗵𝗲 𝗿𝗲𝘀𝗰𝘂𝗲 and with some 𝙛𝙞𝙡𝙩𝙚𝙧𝙨

𝘾𝙧𝙚𝙙𝙞𝙩: If I remember correctly, I saw a trick on this custom date range slicer years ago from BI Elite!

Let DAX tell the story...

createOrReplace

	table 'Date Slicer'

		calculationGroup
			precedence: 1

			calculationItem 'Last 30 Days' = 
					VAR __Isdatesfiltered =
					    CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) )
					VAR _Day = 30
					VAR __Result =
					    IF(
					        __Isdatesfiltered,
					        SELECTEDMEASURE( ),
					        CALCULATE(
					            SELECTEDMEASURE( ),
					            KEEPFILTERS(
					                DATESINPERIOD( 'Date'[Date], TODAY( ), -_Day, DAY )
					            )
					        )
					    )
					RETURN
					    __Result

			calculationItem 'Last 3 Months' = 
					VAR __Isdatesfiltered =
					    CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) )
					VAR _Day = 90
					VAR __Result =
					    IF(
					        __Isdatesfiltered,
					        SELECTEDMEASURE( ),
					        CALCULATE(
					            SELECTEDMEASURE( ),
					            KEEPFILTERS(
					                DATESINPERIOD( 'Date'[Date], TODAY( ), -_Day, DAY )
					            )
					        )
					    )
					RETURN
					    __Result

			calculationItem 'Last 6 Months' = 
					VAR __Isdatesfiltered =
					    CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) )
					VAR __Day = 180
					VAR __Result =
					    IF(
					        __Isdatesfiltered,
					        SELECTEDMEASURE( ),
					        CALCULATE(
					            SELECTEDMEASURE( ),
					            KEEPFILTERS(
					                DATESINPERIOD( 'Date'[Date], TODAY( ), -__Day, DAY )
					            )
					        )
					    )
					RETURN
					    __Result

			calculationItem 'Current Year' = 
					VAR __Isdatesfiltered =
					    CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) )
					VAR __Result =
					    IF(
					        __Isdatesfiltered,
					        SELECTEDMEASURE( ),
					        CALCULATE(
					            SELECTEDMEASURE( ),
					            KEEPFILTERS( DATESYTD( 'Date'[Date] ) )
					        )
					    )
					RETURN
					    __Result

			calculationItem 'Last Year' = 
					VAR __Isdatesfiltered =
					    CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) )
					VAR __Result =
					    IF(
					        __Isdatesfiltered,
					        SELECTEDMEASURE( ),
					        CALCULATE(
					            SELECTEDMEASURE( ),
					            KEEPFILTERS(
					                SAMEPERIODLASTYEAR( DATESYTD( 'Date'[Date] ) )
					            )
					        )
					    )
					RETURN
					    __Result

			calculationItem All = 
					VAR __Isdatesfiltered =
					    CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) )
					VAR __Result =
					    IF(
					        __Isdatesfiltered,
					        SELECTEDMEASURE( ),
					        CALCULATE(
					            SELECTEDMEASURE( ),
					            REMOVEFILTERS( 'Date'[Date] )
					        )
					    )
					RETURN
					    __Result

			calculationItem Custom = 
					VAR __Isdatesfiltered =
					    CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) )
					VAR __Result =
					    IF(
					        __Isdatesfiltered,
					        SELECTEDMEASURE( ),
					        CALCULATE(
					            SELECTEDMEASURE( ),
					            REMOVEFILTERS( 'Date'[Date] )
					        )
					    )
					RETURN
					    __Result

		measure 'Date period' = MIN('Date'[Date]) & " - " & MAX('Date'[Date])

		measure 'Filter Date Slicer Custom' = IF( SELECTEDVALUE ('Date Slicer'[Date slicer column] ) = "Custom", 1, 0 )
			formatString: 0

		measure 'Title Custom Date Slicer State' = 

				IF(
				    SELECTEDVALUE('Date Slicer'[Date slicer column]) = "Custom",
				    "Choose a custom date range",
				    "Custom selection disabled")

		column 'Date slicer column'
			dataType: string
			sourceColumn: Name
			sortByColumn: Ordinal

		column Ordinal
			dataType: int64
			isHidden
			sourceColumn: Ordinal