DAX measures based on MAX and MIN dates selected in the Time Slicer?

[Anonymous]

Hi all - 

I am working on two measures that I want to be dynamic as I use the time slicer.

I attach one picture of my fact table (Time key - Account key - Value (monthly) - Value (YTD) - Entity key - Account type).

To give you an idea of how my dataset is built, I have the measures for the actual data values.... Periodic data = Sum([Periodic - Periodic]). and similar for YTD data.

Then, let's play with the idea that I want to have the following measure dynamic with the date slicer:
Total Sales = Calculate([Periodic Data]), ACCOUNT = ["SALVSAEB"]

Now, my question is: How would I re-write the measure above in order to get the data for the MAX (and MIN) time period in my time slicer? E.g. if the Time slicer is showing 2019-07-15, I want it to calculate Total Sales for July, since I have monthly data only.

My guess of the issue: I have only monthly data, as you can see in my fact table and calendar dimension table.
I do not know if the time slicer is compatible with this type of data, but I would also be okay with a solution where I could e.g. check box two different months and have my MIN measure and MAX measure do the rest based on the selected dates. The desired outcome is to eventually be able to select two different time periods and have a KPI card that calculates the difference between MAX and MIN date selected (e.g. Sales +12,5 %)

Any advice on how to write such  DAX measures? I would appreciate any helpful comments. 

Regards,
Rasmus 





 

[BillyLin]

I have a time slicer that uses a measure.  The measure considers a recordset a part of a time period based on the following formula. 

FY2021 = IF(AND([AdmitDate]<=DATE(2021,06,30),OR([DischargeDate] >= DATE(2020,07,01),ISBLANK([DischargeDate]))),"1","0")

I have another two measure for FY2022 and FY2023.   The entire dataset is from 7/1/2020 to 6/30/2023, which is divided into 3 fiscal years.  However, when I use the min/max/median function on another field in the dataset, the denominator still takes the entire 3 years of data and not just the time period from the slicer.  How should I approach this problem so that when a user selects all data from a certain time period, the min/max/median values are only based on the time period of that slicer.

[Ashish_Mathur]

Hi,

Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Here is my sample data.

Then create a calendar table and add a column to save year and month.

YM =
YEAR ( 'Calendar'[Date] ) * 100
    + MONTH ( 'Calendar'[Date] )

Create three measures to display the maximum, minimum and difference.

MAX =
VAR maxd =
    CALCULATE ( MAX ( 'Calendar'[YM] ), ALLSELECTED ( 'Calendar'[YM] ) )
RETURN
    CALCULATE ( SELECTEDVALUE ( 'Table 2'[count] ), 'Table 2'[YM] = maxd )

MIN =
VAR mind =
    CALCULATE ( MIN ( 'Calendar'[YM] ), ALLSELECTED ( 'Calendar'[YM] ) )
RETURN
    CALCULATE ( SELECTEDVALUE ( 'Table 2'[count] ), 'Table 2'[YM] = mind )

Diff =
VAR d = [MAX] - [MIN]
RETURN
    d / [MIN]

Now you can use slicer and card visuals.

Best Regards,

Eads

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

[Anonymous]

Hi,

Create 2 measures as below to find the minimum and maximum date from selected range.

MinDate = FORMAT(CALCULATE ( MIN(Dates[Date]), ALLSELECTED(Dates) ), "DD-MM-YYYY")

MaxDate = FORMAT(CALCULATE ( MAX(Dates[Date]), ALLSELECTED(Dates) ), "DD-MM-YYYY")

[TD_Keyera]

Hello  @ras_ile, your solution looks great when selecting granular dates in a slicer. 
I tried to make this work while using a date hierarchy selector (e.g. first quarter) and the results are not reliable. 
Would you have any suggestions for this?