davidkofodhanna's avatar
davidkofodhanna
Advocate I
1 year ago

Calculation group for dynamic date

"๐—š๐—ถ๐˜ƒ๐—ฒ ๐—บ๐—ฒ ๐—ฎ๐—ป ๐—ฒ๐—ฎ๐˜€๐˜† ๐—ผ๐—ฝ๐˜๐—ถ๐—ผ๐—ป ๐˜๐—ผ ๐˜€๐˜„๐—ถ๐˜๐—ฐ๐—ต ๐—ฑ๐—ฎ๐˜๐—ฒ ๐—ฝ๐—ฒ๐—ฟ๐—ถ๐—ผ๐—ฑ๐˜€ & ๐˜พ๐™ช๐™จ๐™ฉ๐™ค๐™ข ๐™™๐™–๐™ฉ๐™š๐™จ"

๐Ÿ“… 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
No RepliesBe the first to reply