cancel
Showing results for
Search instead for
Did you mean:  Helper III

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

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  1 ACCEPTED SOLUTION  Community Support

Hi @ras_ile ,

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.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
2 REPLIES 2  Community Support

Hi @ras_ile ,

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.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it. Anonymous
Not applicable
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")   ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks Top Solution Authors
Top Kudoed Authors
Users online (4,203)